A Combo Box on MainForm to Select Subform Records 2

[ZaZa]

Hi everyone. Please help me if you can.

I have a form ( called "IssueDetailsFORM&quot in continuous view that shows all the issue details for equipment that has been "issued" ( ie taken from stores). The same EquipID can have several issue records. The underlying table for the form looks something like this:

tblTWO
------------------
EquipID
IssueDate
IssuedTo
ReturnedDate

The EqipIDs are stored in a table called EquipData with EquipId as the primary key. It looks something like this:

tblONE
--------------
EquipID
Description
etc, etc, etc


Now there are hundreds of equipment, some already have a previous issue details entered ( because they were taken from stores several times)while others have never been issued and hence have no records in the table.

I want to create a combo box that lists all the equipID so that the user can select the equipment that he wants and have a suform appear with all the previous issue deatils so that he can enter a new issue detail. If there are no previous issue details then I still want the ability to enter a new issue detail for the selected EquipmentId.

Steps I took:
a)I created an unbound form called "MainForm&quot and put an unbound combo box ( using the wizard) with records source as "Select EquipementID from tblEquipdata.....". This allows me to get a list of all my EquipmentId's

b)I Put the IssueDetailsFORM as a subform in "MainForm". Since "MainForm" is unbound I was not able to create a parent-child link.

Here is where it gets dicey..
c)I tried to cannibalize the code created when you create a FindRecord-type combo box( this is option 3 when the wizzard asks you how you want to get the values for the combobox). I put the code on the after update event of the EquipId ComboBox. of the MainForm. The code goes something like this:

Me![IssueDetailsForm].RecordssetClone.FindFirst "[EquipID] = ' " & Me![EquipID] & " ' "
Me![IssueDetailsForm].Bookmark = Me!IssueDetailsForm].RecordssetClone.Bookmark

Of course this is not working but I have used up all my other ieads. Any suggestions? Is it the code or my entire approach that's wrong?

Regards,
ZaZa

 

[jebry]

Hi!

I would recommend binding the main form which would allow you to use the parent/child link to the subform. I would put the combo box on a separate 'search' form with the combo box and command button to open the form using the criteria selected. I think that would give you the most flexibility.

hth
Jeff Bridgham

 

[JoeMiller]

You can still utilize Parent/Child links with an unbound form. I would suggest KEEPING the unbound form for the main and make the combo box select the equipment. For the parent child link you put the NAME of the combo in the Parent entry and the FIELDNAME in the child for the subform. I do this all the time and it works perfectly, no code needed!

HTH Joe Miller
joe.miller@flotech.net

 

[ZaZa]

Thanks guys for responding so quickly,

I tried Joe's suggestions and it worked exactly as I wanted it to!!!! I didn't know it was possible to create that parent-child link with an unbound form. That tip was a jem as I have often tried and failed in the past to do it !

Again thanks Joe and Jeff
ZaZa

 

[jebry]

Hey Joe!

A+! I didn't know you could do that with an unbound form either! Another trick in the bag!

Thanks
Jeff Bridgham