how to store values in subform

[ckib]

Hi everyone, here is my latest challenge. I need to look up values in a table, then store those values into a control on a subform. So, for each record in tblIndustry I need to store the value of the field 'industry'. My code doesn't work if there are multiple industry records for a contact ... because all the values are stored into the same control. Could someone tell me how to "reference" mutiple occurrences in the subform? Here is my code so far ... Thanks!

' find industry records from source contact

strSQL = "SELECT * FROM tblIndustry WHERE (((tblIndustry.in_contactID)=" & lngContactID & "));"

Set myRS = myDB.OpenRecordset(strSQL)

' add new industry values to subform

myRS.MoveFirst

Do Until myRS.EOF
Forms![frmContact]![frmContactIndustry_Sub]![List_industry].Value = myRS!industry
myRS.MoveNext
Loop

myRS.Close
Set myRS = Nothing

 

[ckib]

still looking for a solution to this problem ...

 

[pritcham]

Hi

What type of control are you using on your subform for the List_industry?

Martin

 

[ckib]

The control is a combo box with limit to list = true.

Does the type of control determine how to reference multiple instances?

Thanks.

 

[pritcham]

Hi

I don't have time to look at Access just now but I think you need to look at the ComboBox.Items collection as opposed to the ComboBox.Value - the Value can and will only hold one Value, the Items collection can and does hold 1> values. I think you need to use something like ComboBox.Items.Add("This is the Item")

Hope that helps
Martin

 

[ckib]

I will research that. Thank you!

 

[ckib]

I tried:

Forms![frmContact]![frmContactIndustry_Sub]![List_industry].Items.Add (myRS!industry)

but get the error:

Object doesn't support this property or method.

What I need is the functionality of the AddItem method, but it can only be used when the RowSourceType property is set to 0 (None).

Does anyone know of a way to do this? Is the only alternative setting the RowSourceType property to 0 and programmatically updating/adding/deleting records in the table (bound to the subform)???

Can the RowSourceType property be set to 0 just for this task, then set back to it's normal setting?

Thanks.

 

[pritcham]

Hi

If you're using the items.add method you need to add one item at a time (whereas it looks like you're trying to add all items in your myRS!industry object). To do this you'd need to step through each of the items in your RS and add them one at a time (pseudo code below):

With RS
.MoveFirst
do while not .EOF
combobox.items.add .Fields("industry").Value
.movenext
loop
end with

HTH
Martin

 

[ckib]

I did code a loop:

find industry records from source contact

strSQL = "SELECT * FROM tblIndustry WHERE (((tblIndustry.in_contactID)=" & lngContactID & "));"

Set myRS = myDB.OpenRecordset(strSQL)

' add new industry values to subform

myRS.MoveFirst

Do Until myRS.EOF
Forms![frmContact]![frmContactIndustry_Sub]![List_industry].Items.Add (myRS!industry)
myRS.MoveNext
Loop

myRS.Close
Set myRS = Nothing

 

[pritcham]

Did that do the trick or are you still looking for a solution?

Martin