Solved: Populating a subform from combo box entries

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Charmian

Thread Starter
Joined
Mar 6, 2012
Messages
199
I have a form that has a few combo boxes. The list presented in the 2nd combo box is determined by the input in the first combo box. This works. Now I need to have a subform that is populated when all the combo boxes are filled in.
Eg: They choose a line - depending on the line only teams in that line appear in the 2nd combo box drop down. Then they may type in a part number in a text box - but this could be blank - but that's a problem for later.


1. I can get the subform to populate from the entries of the combo box, but only on the NEXT attempt. The first time I go into the form and populate the combo boxes nothing happens in the subform. If I close the form and go back into it, the subform is populated with the information that was previously in the combo boxes - the combo boxes are blank on going into the form a second time. I have this subform set up as the source object being a query. There is an event setting - On enter and on exit. Should I be using one of those to get it to populate only when the current info is entered?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You should use the Combo's After Update Event to "requery" the subform.
the Syntax is
me.subfromname.Requery
where subfromname is the actual name of your subfrom, which the VBA Editor will provide.
 

Charmian

Thread Starter
Joined
Mar 6, 2012
Messages
199
Will try, another combo problem that I've just seen. When the drop down occurs on the form the user picks one and that populates the block - how do you get other columns to also show. EG I have the partnumber in the box that they have picked, but would also like to display the description while they are on that screen
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I am just about to describe that for another poster.
Basically you either use the Combo's SQL query or an actual query to concatenate the 2 columns using a new Column Heading to replace the other 2 columns ie.

ProdDesc: [Product] & " - " & [Description]

where you use your proper field names in place of the Product & Description.
 

Charmian

Thread Starter
Joined
Mar 6, 2012
Messages
199
You should use the Combo's After Update Event to "requery" the subform.
the Syntax is
me.subfromname.Requery
where subfromname is the actual name of your subfrom, which the VBA Editor will provide.
HI,
The error I'm getting now is
Me.Q_Emergency2.Requery is method or data member not foundd

I've tried it with a few variations but he subform doesn't get updated. The query that is used to create the subform is:
SELECT OPERATIONCG.opsname AS Operation, WorkstationCG.wsname AS [Works Station], asset.assetno, Asset.EQSerialNo, Asset.Torque AS [Asset Torque], Asset.Setting AS [Asset Setting], Operationcg.OPSTorque AS [Operation Torque], Operationcg.OPSSet AS [Operation Setting]
FROM ((Asset INNER JOIN WorkstationCg ON WorkstationCG.wsid=Asset.CurrentWSID) INNER JOIN operationcg ON Operationcg.opswsid=workstationcg.wsid) INNER JOIN TEAMCG ON TeamCG.teamid=WorkstationCG.WSteam
WHERE Asset.Assetno=Forms!frm_TechnicianLineAssist!Text35;


And the VB code is:

Private Sub Text35_AfterUpdate()
MsgBox "in Text35_afterupdate" This happens
Me.[Q_Emergency2 subform].Requery
MsgBox "in Text35_afterupdate after the gap subform command" This happens
Me.Q_Emergency2.Requery This gives the error
MsgBox "in Text35_afterupdate after the no gap subform command"
End Sub
 

OBP

Joined
Mar 8, 2005
Messages
19,895
OK, try

Me.Q_Emergency2.Form.Requery

Did the VBA Editor provide the name of the Subform?
 

Charmian

Thread Starter
Joined
Mar 6, 2012
Messages
199
I am just about to describe that for another poster.
Basically you either use the Combo's SQL query or an actual query to concatenate the 2 columns using a new Column Heading to replace the other 2 columns ie.

ProdDesc: [Product] & " - " & [Description]

where you use your proper field names in place of the Product & Description.
This is not appearing in the form even though I have made the Bound Column the concatenated one; if I run the query stand alone - it works.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The bound column should normally be the Key field.
What column number is the new column and what "Width" is it set to?
 

Charmian

Thread Starter
Joined
Mar 6, 2012
Messages
199
The Editor did give me the form when I decided to use it's prompts.
Its working now - thank you
 

Charmian

Thread Starter
Joined
Mar 6, 2012
Messages
199
The bound column should normally be the Key field.
What column number is the new column and what "Width" is it set to?
Changing the bound column didn't work - so the new column is 5cm and it does appear in the drop down. But when you click/move to the next combo box the one you chose in the previous combo box , is just the asset number not the asset & description one - or must I put that FIRST in my SELECT?

Don't worry, OBP - I put it first in my Selection statement and whoopsie - its there - sorry, some days the blonde isn't just out of the bottle!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
A nice bit of learning today, subform requerying, field concatenating and combo manipulation.
Not bad Ha.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top