Loop through combo boxes and add to table 1

[dcurtis]

I am working on a membership database for a Rod and Gun Club that records member information, along with their interests. I have the following relevant tables:

Members
MemberID (PK)
Name......

InterestCategories
InterestCatID (PK)
InterestCatDesc

Interests
InterestID (PK)
InterestCatID (FK)
InterestDesc

MemberInterests
MemberID (FK - Members)
InterestID (FK - Interests)

I have a form that the secretary uses to record member information along with their interests. On that form I have a section for interests, with headers for categories. Under those headers I have combo boxes that select interests for that category:
(SELECT InterestID, InterestDesc FROM Interests WHERE InterestCatID = (Whatever category the combo box is under).

What I would like to be able to do is enter/update the members information and cycle through each combo box(all Tags have been set to "Interests") and append that to the MemberInterest table with the MemberID.

I have tried to work with the Tag property and use an append query:

Dim ctl As Control
Dim SQL As String

SQL = "INSERT INTO MemberInterests (MemberID, InterestID) VALUES (txtMemberID, [b]cboInterest1[/b])

For Each ctl In Me.Controls
If ctl.Tag = "Interest" Then
DoCmd.RunSQL SQL
End If
Next ctl

What happens is I get the same number of entries in the table as there are combo boxes with whatever is in cboInterest1. I know that's because that's what the SQL tells it to do, so I am half way home.....

What I need help with is telling the SQL to look at each combo box and add entries from each one, not just the first one. I am hoping to get some code that will loop through each combo box and take it's value and append it to the table.

Thanks in advance for any help.

 

[PHV]

Something like this ?
For Each ctl In Me.Controls
If ctl.Tag = "Interest" Then
If Trim(ctl.Value & "") <> "" Then
SQL = "INSERT INTO MemberInterests (MemberID, InterestID) VALUES (" & txtMemberID & "," & ctl.Value & ")"
DoCmd.RunSQL SQL
End If
End If
Next ctl

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dcurtis]

Exactly like that. Thanks!

 

[dwight1]

Guys,

I am stuck with exactly the same problem. But i am not so well versed with coding.

I have a combo that has custid and name (comes from the custtable). I had created another loan reference table that has LoanNumber (autoincrement) and True/False field in it.

What i did was, when the user selects a customer in the combo and checks the true/false field it generates and autoincrement number in the loanreference table. So i have got a loan number.

Now i want the borrower id from the combo to be inserted in loan reference table.

In short i want Custid from the combo and Loan number (autogenerated) to be populated in the LoanReference table.

Hope i am clear.

Some thing like this.

Loanreferencenumber Borrower id True/False
(autogenerated) from the combo) (checkboxonform)
70000645 01-060540 True


Thanks
Dwight