Stumpped on a dlookup problem between two tables
Posted on 2004-08-19
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]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")) Then
MsgBox "Use Form to Add New Member"
Check = "NoUpdate"
DoCmd.OpenForm "updatemembers", , , , acFormAdd, , Me.MemberID
Me.LastName = DLookup("[LastName]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")
Me.FirstName = DLookup("[FirstName]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")
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.