Problem with data entry form and multiple values

[akirk]

Need to find a way to store multiple values in my access 2002 database. Each record in the main table can be associated with multiple products. I have a table set up for product name, (tblProduct), my main table (tblMain), and a third table to store the values for products associated with each record (tblProductUse)

I am working on the form for entering data, and am trying to find a way to use a list box in a subform to allow the user to choose multiple product names and store the values in tblProductUse The main table and product use table are joined by the main record ID.

any help is appreciated

 

[Nowell]

Hi, a simple way would be to use combo boxes that look up the products, and when selected they are appended to the main table.

 

[Eupher]

This sounds like a pretty classic one-to-many relationship, and it looks like you are on the right track with your table setup. In this case I would use either a listbox or a subform, but not both - seems redundant to me. My first choice would be a listbox. Assuming your form is bound to tblMain, in general terms, here's how I would set it up:

1) Create a combo box on your form and set its Row Source Type to Table/Query, then set the Row Source to tblProduct. Then you'll have to set your bound column and which column(s) you wish to be displayed in the combo drop-down.

2) Create a list box on your form and set its Row Source Type to Table/Query, then use the query builder to set your Row Source (i.e. click in the Row Source field, then click the button with 3 little dots at the right end of the field). Here you basically design a query. You'll want to include tblProductUse in your query, and select whatever fields you wish to display. Assuming you have a field on your form which displays the record ID field (I'll call it "RecID" for our purpose), include the RecID field in the query grid, and set the criteria for the field to [Forms]![frmMyForm]![RecID] (where frmMyForm is the name of your form).

3) Create a command button and name it something appropriate, i.e. btnAddProduct. In the On Click event, create an event procedure, and put in code that looks something like this:

Dim CurDB As Database
Dim Rs As Recordset
Dim SQLStmt As String

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset("tblProductUse")
Rs.AddNew
'change the field names in the next 2 lines
'as appropriate for your form and your table
Rs![RecID] = Me![RecID]
Rs![ProductName] = Me![ProductName]
Rs.Update
Rs.Close

Set CurDB = Nothing
Set Rs = Nothing
SQLStmt = vbNullString

That should get you started. Post back if you need more details.

Ken S.

 

[Eupher]

p.s. I forgot something. Add...

Me!lstMyListBox.Requery

... right after Rs.Close

Ken S.

 

[akirk]

Ken,

Thanks a bunch, I seem to be on the right track.

In a nutshell, the combo box is used so that each time a product is selected, the button will append it to the productuse table, and then the list box will show it in the main form, correct?

I am getting an error message run time error 13 type mismatch on this line: Set Rs = CurDB.OpenRecordset("tblProductUse&quot

Thanks again

 

[Eupher]

Yes, that's the idea.

Re: the run-time error... Open any code window, then select Tools->References on the menu. What references are checked? What version of Access are you using?

Ken S.