If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Having a difficult time with a lookup
I am still new at trying to add the "bells and whistles" to Access.
I have a question concerning lookups. I have a database that has two tables: BBBMembers and MemberTrainingLog. I also have a form named MemberCPETrainingLog. What I want to be able to do is this: In the MemberCPETrainingLog form, I want to be able to input in the member ID and have the corresponding member’s first name come up in the First Name field of the same form. I also want the last name field in the form to be filled in the same way. The function should look at the BBBMembers table for the information. Can this be done? I have tried looking at DLookups, but I am getting all turned around. Thanks! -- Regina Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Having a difficult time with a lookup
Your question is about basic functionality, not bells and whistles. DLookup
is not the best way to go about this. Unless there is a specific reason you want to search by number rather than name, I suggest using the combo box wizard (use the "Find a record..." option) to set up a combo box that includes the MemberID as a hidden (zero-width) column in the combo box Row Source, which would also include LastName and FirstName. This should give you basic search functionality. It's more work, but more versatile, to make a query (I will call it qryFind) that includes MemberID in the first column, and something like: FullName: [LastName] & ", " & [FirstName] in the second column. Create a combo box (cboFind), and set the following combo box properties: Row Source: qryFind Bound Column: 1 Column Count: 2 Column Widths: 0",1.5" Click the event tab for the combo box Property Sheet. Click After Update, click the three dots, and click Code Builder, OK if the code window does not open directly. The cursor should be blinking between the Private Sub and End Sub lines. Add the following code: Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[MemberID] = " & Me.cboFind Me.Bookmark = rs.Bookmark This assumes MemberID is a number field. Use your own field and control names where I have used placeholder names. rp3886 wrote: I am still new at trying to add the "bells and whistles" to Access. I have a question concerning lookups. I have a database that has two tables: BBBMembers and MemberTrainingLog. I also have a form named MemberCPETrainingLog. What I want to be able to do is this: In the MemberCPETrainingLog form, I want to be able to input in the member ID and have the corresponding member’s first name come up in the First Name field of the same form. I also want the last name field in the form to be filled in the same way. The function should look at the BBBMembers table for the information. Can this be done? I have tried looking at DLookups, but I am getting all turned around. Thanks! -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Having a difficult time with a lookup
BTW, my response a few minutes ago assumes Access 2003 or earlier. For 2007
or later the procedure will differ somewhat, but I do not have Access 2007 in front of me, so I cannot provide specifics. rp3886 wrote: I am still new at trying to add the "bells and whistles" to Access. I have a question concerning lookups. I have a database that has two tables: BBBMembers and MemberTrainingLog. I also have a form named MemberCPETrainingLog. What I want to be able to do is this: In the MemberCPETrainingLog form, I want to be able to input in the member ID and have the corresponding member’s first name come up in the First Name field of the same form. I also want the last name field in the form to be filled in the same way. The function should look at the BBBMembers table for the information. Can this be done? I have tried looking at DLookups, but I am getting all turned around. Thanks! -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Having a difficult time with a lookup
Thanks! I actually do have Access 2007, but I think that I will be able to
adapt it, since I regularly work between 2003 and 2007. It never dawned on me to do it that way. I am doing a lot of complicated things to a few databases right now that sometims I forget that there are easy solutions. Thanks again! BruceM wrote: BTW, my response a few minutes ago assumes Access 2003 or earlier. For 2007 or later the procedure will differ somewhat, but I do not have Access 2007 in front of me, so I cannot provide specifics. I am still new at trying to add the "bells and whistles" to Access. [quoted text clipped - 12 lines] Can this be done? I have tried looking at DLookups, but I am getting all turned around. Thanks! -- Regina Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|