• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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]", "Member Roster", "[MemberID] = '" & Me.MemberID & "'")) Then
    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
0
alexmauer
Asked:
alexmauer
  • 6
  • 5
  • 3
2 Solutions
 
rockmuncherCommented:
Is the [Member Roster].[MemberID] field a text field?  
If it's a number or autonumber field then the single quotes will mean that the Dlookup will never get a match.
0
 
alexmauerAuthor Commented:
it's a text field

Everything is a text field except for the unique # and date
0
 
rockmuncherCommented:
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?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rockmuncherCommented:
Same with 'Member ID' field
0
 
alexmauerAuthor Commented:
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)
0
 
rockmuncherCommented:
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.
0
 
ahmedbahgatCommented:
i normally do it this way:




Private Sub MemberID_AfterUpdate()
Me.FirstName = ""
Me.LastName = ""

Dim Rs As RecordSet
Set Rs= CurrentDB.OpenRecordSet("SELECT [Member Roster].* FROM [Member Roster]" & _
" 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

cheers
0
 
alexmauerAuthor Commented:
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
0
 
ahmedbahgatCommented:
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      
0
 
rockmuncherCommented:
You need to avoid using spaces in access object names at all costs, even though microsoft expound the virtues of being able to use them.  They are bad, bad, bad.  The same goes for special characters that cannot be guaranteed to be benign (ie. %, *, ?, ", ' etc.).

You should absolutely avoid using DLookup if you can.  It is really slooooow and as a result won't update visible fields in a timely manner.  This annoys the hell out of users.

----------

And another way to do what you are trying to achieve would be to use an unbound combo box on the first form.  The combo box would be populated via a query rowsource, and displaying (say) MemberID, Firstname & " " & Surname.  Users can make use of the inherent 'typeahead' of the combo box for speedier matches.  

The AfterUpdate event of the combo box would perform a FindRecord on the form which has all necessary fields bound to the Member Roster table.  This means that when the record is found, all the fields are automatically displayed from the underlying recordsource.  You would need have a subform for the OCMemberPayment data, linked by MemberID.  This subform would be a tablular layout I would guess and again, things would be displayed automatically.

So now all you need to handle is the NotInList event where a user types in a value which doesn't exist in the combo box's rowsource data.  This happens in almost the same way that you are doing things now.  You would open the Members Roster form in Data Entry mode (dialog), then requery the combo box after saving the record (ie. closing the data entry form), then performing a FindRecord to get to the new record.
0
 
alexmauerAuthor Commented:
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("SELECT [Member Roster].* FROM [Member Roster]" & _

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?

0
 
alexmauerAuthor Commented:
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!
0
 
alexmauerAuthor Commented:
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("SELECT [Member Roster].* FROM [Member Roster]" & _
" 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, It seems to me that the open record set would be the table OCMemberPayment - as that is where the data in the form is getting stored.

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.
0
 
ahmedbahgatCommented:
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





0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now