Solved

Stumpped on a dlookup problem between two tables

Posted on 2004-08-19
14
347 Views
Last Modified: 2012-06-27
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
Comment
Question by:alexmauer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11849129
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
 

Author Comment

by:alexmauer
ID: 11849136
it's a text field

Everything is a text field except for the unique # and date
0
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11849150
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 7

Expert Comment

by:rockmuncher
ID: 11849159
Same with 'Member ID' field
0
 

Author Comment

by:alexmauer
ID: 11849196
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
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11849205
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
 
LVL 16

Accepted Solution

by:
ahmedbahgat earned 300 total points
ID: 11849222
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
 

Author Comment

by:alexmauer
ID: 11849276
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
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 11849326
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
 
LVL 7

Assisted Solution

by:rockmuncher
rockmuncher earned 200 total points
ID: 11849353
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
 

Author Comment

by:alexmauer
ID: 11852932
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
 

Author Comment

by:alexmauer
ID: 11854115
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
 

Author Comment

by:alexmauer
ID: 11854914
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
 
LVL 16

Expert Comment

by:ahmedbahgat
ID: 11857611
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question