Want to enter a MemberID and have form display member data

[DDave]

I have a form to enter members data. In order to go to a specific MemberID to edit his information, I have to click on the MemberID field and page down to the ID I want. I would like to have a blank form with the cursor in a MemberID field. When I type in the MemberID the member record is displayed in the fields.


This seems very basic but I have spent hours looking at tutorials to no avail.

[alansidman]

This may work for you

http://www.datapigtechnologies.com/f...tomfilter.html

Alan

[DDave]

I would rather type a value than scroll through the list - Scrolling through the list is what I have to do now. I will consider how to modify what the video does and see if I can make it do what I want. Thank you very much for your help.

[alansidman]

In that case, this should help you in your modification

http://www.datapigtechnologies.com/f...dsetclone.html

Alan

[DDave]

That looks much better.I'll try it in the morning and report back.
Again Thank you very much for your quick reply.

[DDave]

I have 2 tables. Members storing name, etc and Dues Paid storing a record every time the member pays dues.
After adding the combo box and assiciated VB, when I type a MemberID and press enter, the Dues detail changes but the Member stuff doesn't.

[alansidman]

Missing something in the code to have it search the member table as well as the dues table. Suggest you post your db for more extensive review. Pictures are nice but need to see table names and field names, etc.

[DDave]

Ok, stripped out data and many queries and reports. Attached is Zip.
I certainly appreciate your help.

[Alex Motilal]

Try this:
Put a new Combo Control and name it 'ComboMemberID'

Row Source:
SELECT [Members].[MemberID] FROM Members;
After Update Event:
Private Sub ComboMemberID _AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MemberID] = " & Str(Nz(Me![ ComboMemberID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Remove the original combo control before trying this


Alex

[Alex Motilal]

You should also add the field MemberID and set the visible property to No.

Alex

[DDave]

I deleted the old ComboBox.
I don't find a Combo Control choice in Design.
I assumed you mean Combo Box (Form Control).
I put in a new Combo Box and called it "ComboMemberID".
I right clicked on it and selected Properties.
I changed the name at the top to "ComboMemberID".
In the Row Source line I put SELECT [Members].[MemberID] From Members; - I closed properties.
I again right clicked on the new box.
This time I choose Build Event.
Then I choose Code Builder.
A new screen appeared. ComboMemberIS was on the left and BeforeUpdate on the right.
I highlighted After Update and entered.

Private Sub ComboMemberID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MemberID] = " & Str(Nz(Me![ ComboMemberID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I do not understand add the Field MemberID and set to visible property to No - Add it where?

When I type a valid number in the Combo Box, I get error "can't find the object 'Private Sub ComboMemberID_AfterUpadte().'

Have tried many things to no avail.
Thank you for your help, Alex, in advance.

[Alex Motilal]

In the design view of the form click 'Add Existing Fields' in the Design tab on the ribbon and drag 'MemberID' field to the main form. Click the Format properties of the new text control and select No of the visible property.

Check the code I sent is on the After Update Event of the Combo.

If the changes you have made did not succeed, upload the DB with changes you made.

Alex

[DDave]

I tried to follow your directions, but several questions arise. I'm attaching the zipped mdb. If this doesn't work, I'll try something else after studying more. There always seems to be something come up I can't answer.
Thank you in advance.

[Alex Motilal]

The changes I suggested works fine in my system (Office 2007). However I am not able to upload the Zipped file. Once again you upload the file with the changes I suggested, so that I can analyze the problem.
Alex

[DDave]

Appreciate your attempt at help. I was able to download the zipped file and got the same errors as before zipping it. Here is another zipped file. Thank you.