alexmauer
asked on
Stumpped on a dlookup problem between two tables
Hello,
I am pretty new to access and DB's and I am stuck on a doosie.
I have a DB with 2 table:
1. OCMemberPayment (a stores daily transactions) - the feilds in there are:
| Transaction # | Member ID | Payment Date| Payment ID | Payment ID | Chit_Checknumber | Last Name | First Name | Notes | Ticket Number |
(transaction # is the prime key)
2. Member Roster (table stores member info)
| Unique # | Member ID | First Name |Last Name |
(Unique # is prime key)
I have 2 forms designed to for data entry and member table update.
OCMemberPayment is the data entry form used to record all daily transactions.
What I want to have happen is that as they type in the member ID # (first field in the form), after update will compare the member number to the [Member Roster] and if it finds the member # in the table, it will populate the first and last name fields in the transaction form
If it doesn't find the #, it will throw an msg saying that the number should be entered. At this point, the second form will open and the member Roster table can be updated with ne member info.
What it currently does and does not do.
It recognises the unknown # and will open the second form. It will also record the new # to the members table - works like a charm
It does not compare the typed in # to the Member Roster table - so it always opens the second form.
I will include the afterupdate code:
Private Sub MemberID_AfterUpdate()
Me.FirstName = ""
Me.LastName = ""
If IsNull(DLookup("[LastName]
MsgBox "Use Form to Add New Member"
Check = "NoUpdate"
DoCmd.OpenForm "updatemembers", , , , acFormAdd, , Me.MemberID
Else
Me.LastName = DLookup("[LastName]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")
Me.FirstName = DLookup("[FirstName]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")
End If
End Sub
I creatd the data entry form from the wizard and all the fields are pointing to the OCMemberPayment table. The MemberID, Last Name and First name fields are unbound (thus when the form is filled out, it does not record that data to table) If I bind the memberID to the transaction table it will record the number, but I can't bind either name fields - gives a runtime error
As much as I can tell, it is not seeing the member roster table at all and thus cannot compare the typed number to a match in the member roster - therefore the comparrison never hapeens
I'm totally stumped any help would be great. Sorry this is so long winded.
thanks
I am pretty new to access and DB's and I am stuck on a doosie.
I have a DB with 2 table:
1. OCMemberPayment (a stores daily transactions) - the feilds in there are:
| Transaction # | Member ID | Payment Date| Payment ID | Payment ID | Chit_Checknumber | Last Name | First Name | Notes | Ticket Number |
(transaction # is the prime key)
2. Member Roster (table stores member info)
| Unique # | Member ID | First Name |Last Name |
(Unique # is prime key)
I have 2 forms designed to for data entry and member table update.
OCMemberPayment is the data entry form used to record all daily transactions.
What I want to have happen is that as they type in the member ID # (first field in the form), after update will compare the member number to the [Member Roster] and if it finds the member # in the table, it will populate the first and last name fields in the transaction form
If it doesn't find the #, it will throw an msg saying that the number should be entered. At this point, the second form will open and the member Roster table can be updated with ne member info.
What it currently does and does not do.
It recognises the unknown # and will open the second form. It will also record the new # to the members table - works like a charm
It does not compare the typed in # to the Member Roster table - so it always opens the second form.
I will include the afterupdate code:
Private Sub MemberID_AfterUpdate()
Me.FirstName = ""
Me.LastName = ""
If IsNull(DLookup("[LastName]
MsgBox "Use Form to Add New Member"
Check = "NoUpdate"
DoCmd.OpenForm "updatemembers", , , , acFormAdd, , Me.MemberID
Else
Me.LastName = DLookup("[LastName]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")
Me.FirstName = DLookup("[FirstName]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")
End If
End Sub
I creatd the data entry form from the wizard and all the fields are pointing to the OCMemberPayment table. The MemberID, Last Name and First name fields are unbound (thus when the form is filled out, it does not record that data to table) If I bind the memberID to the transaction table it will record the number, but I can't bind either name fields - gives a runtime error
As much as I can tell, it is not seeing the member roster table at all and thus cannot compare the typed number to a match in the member roster - therefore the comparrison never hapeens
I'm totally stumped any help would be great. Sorry this is so long winded.
thanks
ASKER
it's a text field
Everything is a text field except for the unique # and date
Everything is a text field except for the unique # and date
OK. Another thing to check. In you description of the OCMemberPayment table you have spaces in the fieldnames 'Last Name' and 'First Name'. You have no spaces in your DLookups. Should there be spaces?
Same with 'Member ID' field
ASKER
Well, I thought of that, and I changed them all and I got errors
The member roster table is MemberID, FirstName and LastName -same as the code
The OCMemberPayment has spaces, but the code doesn't reall point to that, only the member roster
(member roster does have a space)
The member roster table is MemberID, FirstName and LastName -same as the code
The OCMemberPayment has spaces, but the code doesn't reall point to that, only the member roster
(member roster does have a space)
OK. There's an easy way to work this out.
Create a new query in design view.
Add the member roster table.
Select the Member ID, Firstname and LastName fields for output.
Add a valid criteria expression to the Member ID field (eg. ="bob1234")
Run the query and make sure you get some data back.
Once that is done, switch to SQL view and copy the field names and where clause structure into the relevant bits of the DLookup expression. This should solve whatever little problem exists.
Create a new query in design view.
Add the member roster table.
Select the Member ID, Firstname and LastName fields for output.
Add a valid criteria expression to the Member ID field (eg. ="bob1234")
Run the query and make sure you get some data back.
Once that is done, switch to SQL view and copy the field names and where clause structure into the relevant bits of the DLookup expression. This should solve whatever little problem exists.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That gives me two options. Let me go to work on these and see what sort of results I get
Thanks for all your help
Thanks for all your help
sure, however with my respect, the dlookup method is bad, because you will query the data 3 times within your code, however with the recordset method it only be queried once
cheers
cheers
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi all thanks for the help.
Put the code in that ahmedbahgat gave me and I get a runtime error 13 - type mismatch. It is tripping at this line:
Set Rs= CurrentDB.OpenRecordSet("S
Do I need to specify the currentdb and Open record set?
Also, do I need to set the field in the form to point to the Member Roster table?
Put the code in that ahmedbahgat gave me and I get a runtime error 13 - type mismatch. It is tripping at this line:
Set Rs= CurrentDB.OpenRecordSet("S
Do I need to specify the currentdb and Open record set?
Also, do I need to set the field in the form to point to the Member Roster table?
ASKER
I've another question, I seem to kill my form if I try and set my control source to the OCMemberPayment LastName Column and FirstName column. At the end of the data entry, I want to store the returned names to the OCMemberPayment table.
This whole thing has me twisted around - I think I moght officially be bald!
This whole thing has me twisted around - I think I moght officially be bald!
ASKER
Does it matter if my main form was created using the wizard and it pointed to the OCMemberPayment table (transaction log)?
When I look at this code:
Private Sub MemberID_AfterUpdate()
Me.FirstName = ""
Me.LastName = ""
Dim Rs As Recordset
Set Rs = CurrentDb.OpenRecordSet("S
" WHERE ((([Member Roster].[MemberID])='" & Me.MemberID & "'));")
If Rs.RecordCount = 0 Then
MsgBox "Use Form to Add New Member"
Check = "NoUpdate"
DoCmd.OpenForm "updatemembers", , , , acFormAdd, , Me.MemberID
Else
Me.LastName = Rs!LastName
Me.FirstName = Rs!FirstName
End If
Rs.Close
End Sub
And I see the Rs=CurrentDB.OpenRecordSet
I just can't seem to get to the member roster to look up the member Numbers. No ampount of outside research or book research has helped. I am too new to access to fight myself out of this corner I've painted myself into.
Please help.
When I look at this code:
Private Sub MemberID_AfterUpdate()
Me.FirstName = ""
Me.LastName = ""
Dim Rs As Recordset
Set Rs = CurrentDb.OpenRecordSet("S
" WHERE ((([Member Roster].[MemberID])='" & Me.MemberID & "'));")
If Rs.RecordCount = 0 Then
MsgBox "Use Form to Add New Member"
Check = "NoUpdate"
DoCmd.OpenForm "updatemembers", , , , acFormAdd, , Me.MemberID
Else
Me.LastName = Rs!LastName
Me.FirstName = Rs!FirstName
End If
Rs.Close
End Sub
And I see the Rs=CurrentDB.OpenRecordSet
I just can't seem to get to the member roster to look up the member Numbers. No ampount of outside research or book research has helped. I am too new to access to fight myself out of this corner I've painted myself into.
Please help.
the first problem may be because you need to include the DAO3.6 liberary in the referneces screen, open any module in design and from the tolls menu bar select references and make sure to include Microsoft DAO 3.6 liberary
second question, you do not need to set any field to anything really, as long as you have a field on the form where the user will type in the MemberID, the member IFD bound to what? it does not really matter, it is up to you to bound it to any field you like from the underlaying query
third question, again it does not matter if the wiard was involoved, as long as, please read my previous parahraph
the fourth question, the OpenRecordSet is one of the methods that is associated with the CurrentDB opject and the recordset is opened temperorly within the code to check if the entered MemberID " enetered by the user within the entry form" is existining or not within the Member Roster table, the recordset will be closed at the end of the sub
hope i answered all your questions
cheers
second question, you do not need to set any field to anything really, as long as you have a field on the form where the user will type in the MemberID, the member IFD bound to what? it does not really matter, it is up to you to bound it to any field you like from the underlaying query
third question, again it does not matter if the wiard was involoved, as long as, please read my previous parahraph
the fourth question, the OpenRecordSet is one of the methods that is associated with the CurrentDB opject and the recordset is opened temperorly within the code to check if the entered MemberID " enetered by the user within the entry form" is existining or not within the Member Roster table, the recordset will be closed at the end of the sub
hope i answered all your questions
cheers
If it's a number or autonumber field then the single quotes will mean that the Dlookup will never get a match.