Thanks NeoPa, that did the trick! I thought it could all be done in a query, I just couldn't wrap my mind around the logic. I also found out something interesting about using TOP 1....it will return multiple records if they meet the criteria (I didn't include the ID field in the ORDER first time around), didn't know it would do that.
Jim, thanks for the time you spent on your method. I had already started working on some VBA along...
bard777
Last Activity: Jul 16 '10, 09:02 PM
Joined: Jan 14 '08
Location: My Cube
-
Thanks Jim...I had already started donig something similar so I went with that (for now anyways).
Here is what I have, but it is not working:
...Code:Set db = CurrentDb Dim rsDEDUPE As Recordset Dim varLN As String Dim varTic As Long Dim varToc As Long varTic = 0 ' count total deleted duplicate records varToc = 0 ' count total for duplicate recordset Set rsDEDUPE
Leave a comment:
-
You just need a RIGHT JOIN (or LEFT JOIN) instead of an INNER JOIN.
In the Query Design window for your query right click on the line between [Main] and [Mux] and choose properties. Then choose the option that say ALL RECORDS FROM [Main]. If you look at the SQL view it should look like the code below:
or...Code:SELECT Main.f_1, Main.f_2, mux.d_1 FROM mux RIGHT JOIN Main ON mux.mux_ID = Main.Main_ID;
Leave a comment:
-
Sorry, I forgot to say that in your form "totalize_form_ name" you will need to have a textbox to hold the openargs value you are passing it (the textbox can be not visable if you want).
Let's say the textbox is txt_OAV. In the OnLoad event for the form "totalize_form_ name" enter the code below:
Then in the query to gather all your totalized data you can use...Code:Me.txt_OAV = Me.OpenArgs
Leave a comment:
-
Yes, I have an ID field, so this is a good lead, thanks.
It is possible that [eDate] could be the same as well, but in this case either will do. The real issue is there is no data validation on the front end....Leave a comment:
-
Not sure if I totally understand what you are doing, but perhaps this would work:
In your onclick event (Code Builder) -
...Code:DoCmd.OpenForm "totalize_form_name", acNormal, , , acFormEdit, , Me.SubformFieldName
Leave a comment:
-
Removing Duplicates based on most recent
I fully expect to have a duh moment when I read replies to this....BUT....
I have a table with duplicates in the [LN] field (could be 2 records with same value, might be 7 records). I need to delete all but the the most recent based on the [eDate] field.
I hope this is enough info.
Thanks. -
I do not need the space in there, I added that after what you suggested didn't work. I tried again in case I missed something the first time, but it still gives the "No Current Record" error.
It seemed like "no current record" is other than a Null value, so Nz can't handle it. That is why I tried to catch it with EOF, but I had no recordset defined.
Here is what worked:
...Code:Dim
Leave a comment:
-
No Current Record
I am running some queries on form load to get some summary info on a database.
My query is summing records based on a priority field value (1, 2, or 3) and may find none. When there are no records that meet the criteria for a given priority the line that updates the textbox with the value errs out (below).
here is my code for the textbox:
...Code:Me.txt_low_cnt = Nz((db.OpenRecordset(strSQL)(0)) &
-
I must be asking questions too soon...thsi si the 2nd one I have answered for myself.
I needed to use Column, as in:
Me.lst_owner.Co lumn(1)
This gives me the owners name vs. the autonumber associated with their record in the lookup table.Leave a comment:
-
Grabbing displayed value of dropdown
I have a form that includes a dropdown getting it's values and display from a lookup table. The value is just the autonumber for the row and the display text is a name field. This works great for the overall functionality, but when I log an action I would like to write the display value to the log since the actual autonumber value would be useless.
Anyone know how to do this? Seems like I halfway remember doing something like:... -
I figured it out....thanks to:
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
I used Conditional Formatting (on the Format menu in form design.) and just set the condition to "Less Than" and the value to "Now()".Leave a comment:
-
Change textbox forecolor in Tabular form
I am creating a project management DB for our company. I have a project form that has a requirements tabular subform. I want to change the due date textbox to red if the sue date has pasts.
I have been able to do this, but it seems to be evaluating the first value for this text box and setting the forecolor for all records instead of evaluating them individually.
Here is my code:
...Code:' Flag requirements
-
Complicated Query Trouble
I hope I can describe what I am trying to do here...
I have 3 tables
LC_case
LC_ID
LC_name
LC_loans
LC_LN_ID
LC_LN
LC_LN_2
LC_loss
LS_LN
LS_amt
I am passing a value from a text box called fld_lc_id for LC_ID.
I need something like:
...Code:Select LC_ID, LC_name, LC_LN, LC_LN_2, LS_LN, LS_amt
-
I got it to work using the following:
fld_pln_num represents a textbox on the form that executes the insert.Code:INSERT INTO Table 1 (PLN_NUM, SLN_NUM) SELECT fld_pln_num, SLN_NUM FROM [Table 2] WHERE [Table 2].PLN_NUM = fld_pln_num
Leave a comment:
-
Using a SELECT in an INSERT query
I have a form kicking off an INSERT query. I need to check another table for a matching value and use a field from that table as the value for one of the fields in my insert.
Table 1 (insert from form)
PLN_NUM
SLN_NUM
Table 2
PLN_NUM
SLN_NUM
I need to do something like:
...Code:INSERT INTO [Table 1] (PLN_NUM, SLN_NUM) VALUES (fld_PLN_NUM, (SELECT SLN_NUM FROM
-
Open one Access database from another
I am creating a utility database to use to update and maintenance other databases. One of them has an update process that uses 19 queries and as many linked tables. It works, but is complicated (and undocumented), so I would rather just have a button that opens that database.
I would prefer opening a specific form, but that is optional.
Here is some code (MSDN) I found that gets as far as opening the database, but... -
Record with empty AutoKey showing
I have a Continuous Forms form based on a query that is showing a "blank" record with even the AutoKey field as "Autonumber :"
It is almost as if the "new record" row at the bottom of a table is showing.
************
**WOOT**
************
I just figured it out, but I am going to post anyway in case somebody else has the problem.
I looked at the "Data"... -
Thanks for the Help NeoPa...I see your point.
The other missing info is that I am trying to fix a complete Access app somebody else wrote. Of course no docs or assistance from the "programmer ".
I have made some progress AND bumped into other issues.
I will try and be more precise with my other posts....thanks again.Leave a comment:
-
I don't mean to annoy you...I am asking about a small part of what I am doing so I am trying to avoid changes that will impact the rest of my code.
If I use DCount() won't I need to make a second hit on the table if there are more than one reocrd?...Leave a comment:
No activity results to display
Leave a comment: