combobox list and a subform recordsource

[a7n9]

Hello all,

I've a main form and a subform. In the main form I've a combobox which displays all the possible values for Field1 in Table1. I've a subform which displays all the fields in Table1, but I want this subform to be linked with the combobox. Meaning if I select something in the comobox for Field1, all the remaining fields should be shown in the subform.

Basically, all I want to let the user update the records, but the combox allows the user to type the name in. Therefore, when the user selects a value from combobox list, all the fields for that record should be shown in the subform.

Currently, I've set recordsource for the subform as

SELECT Table1.ID  
FROM Table1
WHERE (((Table1.ID)=[Forms]![update_sch]![combo13])));

update_sch is my main form and combo13 is the combo I'm using, and ID is the field I want to link.

Any help on this is appreciated.

Thanks.

 

[kevinnichols78]

You could handle all of this with code, I did something similar here:

Public Sub Combobox_Change()
Dim dbs As Database, rst As Recordset, strSQL As String
Dim selectedValue as string

selectedValue = combobox.text
strSQL = "SELECT * FROM table1 WHERE ID = '" + selectedValue + "'"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

subForm.text1.value = rst!field2
subForm.text2.value = rst!field3
subFrom.text3.value = rst!field4

'almost forgot this part:
'you need to make sure you close what you open
rst.close
End Sub

If I am not mistaken, this should solve your problem. If the sub form only opens after clicking something else on the main form you will have to modify this code somewhat to make it work for your purposes. Let me know if you have any other questions.

Kevin

 

[Norie]

Set the record source for the subform to SELECT * FROM Table1.

Add all the fields required to the subform, including ID.

If you want you can set the visible property of the ID field to false.

Save the subform and then open the main form.

Make sure the Toolbox Wizard is turned on and add a subform control.

By following the wizard you should create a link between the main form and the sub form.

To update the subform you will need to add code to requery it when the combobox changes.

 

[a7n9]

Thanks Kevin and Norie. Kevin, I'll apply your solution later because I already have the subform ready for Norie's solution. But I like VBA so I'll definitely use yours too.

Norie:

I tried your solution. I got the subform and form linked by ID, but I'm not clear on where should I put the requery code. Should I create a new command button and put the code in it or I can put it in some combo box event? Also, how would the requery code look like?

Thanks.

 

[Norie]

The requery code could go in either a combobox event or command button event.

I would go for the combobox myself.

I'm not 100% sure what the code would be, don't have access to Access right now.

Try checking the help files about referencing subforms.

 

[a7n9]

I think my main form update_sch and update_sch_subform are linked because when I select the subform in its Data property it shows me

Link Child Fields    = ID
Master Child Fields = ID

Also, I put a commad button on the main form on which I've

update_sch_subform.Requery

but when I select a value from the combobox and click the command button nothing happens. The subform doesn't change the data it has.

Any help is appreciated.

Thanks.

 

[a7n9]

Hello all,

I found the solution, the simplest thing would be just to change the recordsource of the subform to

SELECT * 
FROM Table1
WHERE Table1.ID = combo13.value

As I was thinking about this I found an example in the help, I'm copying it from there

The next example changes a form's record source to a single record in the Customers table, depending on the company name selected in the cmboCompanyName combo box control. The combo box is filled by an SQL statement that returns the customer ID (in the bound column) and the company name. The CustomerID has a Text data type.

Sub cmboCompanyName_AfterUpdate()
Dim strNewRecord As String
strNewRecord = "SELECT * FROM Customers " _
& " WHERE CustomerID = '" _
& Me!cmboCompanyName.Value & "'"
Me.RecordSource = strNewRecord
End Sub

Thanks everyone and we can close this topic.