Solved

Stumpped on a dlookup problem between two tables

Posted on 2004-08-19
14
345 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
  • 6
  • 5
  • 3
14 Comments
 
LVL 7

Expert Comment

by:rockmuncher
Comment Utility
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
Comment Utility
it's a text field

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

Expert Comment

by:rockmuncher
Comment Utility
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
 
LVL 7

Expert Comment

by:rockmuncher
Comment Utility
Same with 'Member ID' field
0
 

Author Comment

by:alexmauer
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:alexmauer
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now