Using the current record to store fields into a new table

[ringo196]

What I'm trying to do seems fairly simple, but I'm having trouble making it work.

I have a form that uses a combo box to search for a specific record, and then pulls up said record. In this case, the record comes from a query which has the fields MemberName, MemberID, Swipes, and Email. The Combo box searches for members by typing up the member name, then the form brings up the data of the member you just looked up.

What I'm trying to do, is that on button click, I need to store the MemberName and MemberID onto a new table AttendanceT. I've tried several different methods and what ends up happening is I always end up storing the first record of the query, and not what the current record (that you searched for using the combo box, and whose information are displayed on the form currently).

I have tried Dlookup as well as setting variables to save fields from specific variables such as namestr = rs!MemberName

Any help would be appreciated, let me know if you need more information.


Thanks!


Ringo


Here's what the form looks like, the MemberID information isn't displayed, but is part of the query that the form pulls data from.

[June7]

Controls used to input search criteria must be UNBOUND. Is the combobox UNBOUND?

If you want record in AttendanceT, why isn't this form bound to AttendanceT? Then bind combobox to MemberID field and select member from combobox to create attendance record.

Should not store member name into AttendanceT, only memberID. And I expect you want a date in the record.

Otherwise, to create record in another table, use:

CurrentDb.Execute "INSERT INTO AttendanceT(MemberID, AttendDate) VALUES(" & Me.textboxname & ", Date())"

[ringo196]

Yeah, the combo box is unbound, that part of the form is working perfectly.

There's 2 tables in the database, MembersT, which has member details such as phone numbers, emails, if they want to be on the listserv etc. And the other one is AttendanceT which was already mentioned. AttendanceT has 5 fields, AttendanceID, AttendType (Beginner lesson, intermediate lesson and social dance), MemberName, MemberID and Date (default value is current date). The two tables will be in a relationship using MemberID. I used a query MemberSearchQ that returns parts of the MembersT and this query is what the combo box uses. (I guess I could just pull the records directly from MembersT also)

Here's how I've been adding new records to the AttendanceT table so far. I will try your code though and see if it works!

Dim db As Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("AttendanceT")


rs.AddNew
rs("AttendType").Value = "Beginner Lesson"
'rs("MemberName").Value = DLookup(MemberName, MembersT) -> this is the part that doesn't work correctly


rs.Update

[ringo196]

Update!

I tried the INSERT TO code and I was getting syntax errors. I think I got it to work now though, using this code:

rs.AddNew
rs("AttendType").Value = "Beginner Lesson"
rs("MemberName").Value = Me.MemberName
rs("MemberID").Value = Me.MemberID


Thanks for the help!

[June7]

INSERT INTO

not

INSERT TO