SQL Select found do THIS, N/F do THAT
- Thread starter dcavaiani
- Start date
Not really enough to go on. Really need to define "matching record" and what data you have available to your VBA.
From a high level perspective I'd make a query based on Table1. Then LEFT JOIN Table 2 to it (show all from Table 1). Create a calculated field called 'Table2Match' that resolves to True or False and determines if there's a matching record in Table 2. Then use a Dlookup in your VBA into that query to see if the Table2Match field is True or False.
From a high level perspective I'd make a query based on Table1. Then LEFT JOIN Table 2 to it (show all from Table 1). Create a calculated field called 'Table2Match' that resolves to True or False and determines if there's a matching record in Table 2. Then use a Dlookup in your VBA into that query to see if the Table2Match field is True or False.
MarkK
bit cruncher
- Local time
- Today, 13:01
- Joined
- Mar 17, 2004
- Messages
- 8,472
The basic VBA structure of this problem is as follows . . .
Anything that is missing in this code was missing in your description of the problem.
Code:
Sub YourSub
If ExistsInTable2(FieldInTable1) Then
[COLOR="Green"] 'do this[/COLOR]
Else
[COLOR="Green"] 'do that[/COLOR]
End If
End Sub
Private Function ExistsInTable2(someValue as ???) As Boolean
ExistsInTable2 = DCount("*", "Table2", "SomeField = " & someValue) > 0
End Function
Private Function FieldInTable1 As ???
[COLOR="Green"] 'not sure how you calculate this, probably you need to find a single row first[/COLOR]
End FunctionWhichever method you choose, DLookup or DCount, handle the dates, numbers and strings properly as explained here:
http://allenbrowne.com/casu-07.html
http://allenbrowne.com/casu-07.html
State = DLookup("supplierstate", "suppliers", "supplierid = 7")
State = DLookup("supplierstate", "suppliers", "supplierid = " & [VendorID])
State = DLookup("supplierstate", "suppliers", "supplierid = " & [Forms!supplierinvoicehdr!vendorid])
1st 1 works but not last 2! The field vendorid is from the Header main form and this code is an event on a field in the subform (the RELATED detail records). The header table and detail tables are linked by a recordid. Do I have to do a dlookup back to the header first - based on the recordid ??
Getting confusing. This is the Queries forum, you initially asked about VBA and your last post is just as ambigous.
State =
The above code implies we are working in a module. However,
& [VendorID])
Is only a valid reference to a field in a query.
Where exactly are we working? Query or a Module?
State =
The above code implies we are working in a module. However,
& [VendorID])
Is only a valid reference to a field in a query.
Where exactly are we working? Query or a Module?
VBA - took me a while to see that a SELECT query will NOT work in vba, so need to do the Dlookup instead. Sorry for confusion, but I come from 40 years of IBM/DB2/400 so these formats are really hard for me to understand, but I WILL get there!
Yes you can't write queries directly in VBA, you need something to execute it and/or return records from it.
If State is a String variable remember to catch Null errors in the DLookup() using the Nz() function. If it's also control (like a textbox) then you can put that DLookup() code directly in the Control Source of the textbox.
You mentioned that VendorID is in the subform and if that's the case you can also reference it this way in VBA:
... only in the parent form can you reference it this way.
Here's a decent link on referencing:
http://access.mvps.org/access/forms/frm0031.htm
If State is a String variable remember to catch Null errors in the DLookup() using the Nz() function. If it's also control (like a textbox) then you can put that DLookup() code directly in the Control Source of the textbox.
You mentioned that VendorID is in the subform and if that's the case you can also reference it this way in VBA:
Code:
State = DLookup("supplierstate", "suppliers", "supplierid = " & Me.[COLOR="Blue"]SubformControlName[/COLOR].vendorid)Here's a decent link on referencing:
http://access.mvps.org/access/forms/frm0031.htm
Things are lookin' much better. I have this 1,400 page book on Access, and really cannot find SQUAT on Dlookup - What's up with that?? ;-)
Interesting, was wondering 'bout that - which record it would return - guess more "where " conditions may be appropriate ...
Actually a DLookup() is absolutely fine, it's just slow that's it. It returns an arbitrary record (not the first) based on the criteria you pass to it if its source is a Table. But if it's an ordered query it returns the first record.
You can pass multiple criteria to it so that it returns the record that you desire, and it does well to retain the data type.
Here's a simple example:
By the way tabitha, I haven't forgotten your post. I'm just multitasking... who says men can't multitask
You can pass multiple criteria to it so that it returns the record that you desire, and it does well to retain the data type.
Here's a simple example:
Code:
DLookup("Field", "Table/Query", "ID = " & Me.txtID & " AND DateOfBirth = #" & Me.txtDOB & "#")By the way tabitha, I haven't forgotten your post. I'm just multitasking... who says men can't multitask
More DLookup() examples for you:
http://access.mvps.org/access/general/gen0018.htm
http://access.mvps.org/access/general/gen0018.htm
I'm unclear. Why would it be slow to 'chain' to another Table BY KEYS?? Is there another way, more efficient, to get a record from another table ?
Other methods are using a recordset... one step at a time dcavaiani
When I say slow I don't mean it's crippling slow. You will notice how slow it is if you're using many DLookups on one form, but for one off jobs like yours it's ok.
When I say slow I don't mean it's crippling slow. You will notice how slow it is if you're using many DLookups on one form, but for one off jobs like yours it's ok.
