Referencing a Subform from Multiple Forms

[ArtimusTek]

Hi guys, my first post on Tek Tips. I got a little problem, i stayed back 2 hours at work trying to figure it out but no luck.

I have a subform, that runs a query on another value in that subform.. and i use this to reference it;

Forms![ServiceJobCardMod]![ServicePartsList].Form![ModelCombo]

And it works nicely when im using the main form "ServiceJobCardMod". However, I have other forms that use the same subform.. and I cant use this same referencing style.

Any suggestions? Input is greatly appreciated.

 

[Remou]

I would say the best bet is to build the query dynamically on the subform:

<...>    
strSQL = strSQL & " WHERE fldX = " & Me.ModelCombo
    If DLookup("Name", "MSysObjects", "Name= 'qryQuery'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryQuery")
        qdf.SQL = strSQL
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryQuery", strSQL)
    End If

 

[ArtimusTek]

Yeh i thought that would be a nice way to do it.. but trying to avoid it.

I was thinking, maybe theres a better way to make only 1 form.. instead of 3.

Right now the forms are;
1. A form that is solely for Data Entry
2. A form that is for modifying single records
3. A form that allows the user to look through all records, but not modify any.

 

[Remou]

There is no need for three forms. Forms can be opened with arguments. Have a look at the OpenForm method with particular reference to Datamode.

 

[TheAceMan1]

How are ya ArtimusTek . . .

Use a function in the query that returns the combobox value instead of the form reference:

[blue]Public Sub cbxModel()
   cbxModel = Me!ModelCombo
End Sub[/blue]

I'm not sure how your using the form reference in the query but here's an example of a WHERE clause using the function:

[blue]   WHERE [FieldName] = cbxModel[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[ArtimusTek]

Well guess what.. I figured out how to make one form, but with data entry.. so i only have one form... however..

In the sub form, when i set Model as "123" it sets StoreItem.Column(1).
However, when i set a new record.. and change the Model.. the old StoreItem.Column(1) changes.. and the value disapears!

Do you think the above suggestions will solve this? Or do i need something else...

 

[Remou]

What is the 'it' that sets to StoreItem.Column(1) and where does it set it?