Text and combo boxes

[hllary]

I have a text box where the user enters part of a number. the subform show all record that have part of that number. The combo box show all source of figures associated with that part number. The problem is when I make a selection with the combo box it disregards the text box and shows me all items with that source of figure.

How do I keep the combo box from requerying the selection? I tried to use the code below but I'm getting "method or data member not found" on .RowSource.

Private Sub CbSourceFig_AfterUpdate()
    With Me.txtNSP
        If IsNull(Me![Source of Fig/Item Numbers]) Then
            .RowSource = ""
        Else
            .RowSource = "Select [NSP]" & _
                        "from Master_Totals_Search_qry " & _
                        "Where [SourceFig]=" & (Me.CbSourceFig)
        End If
        Call .Requery
End With
    
End Sub

How to I fix the error or is there a better way of doing this?

 

[jdraw]

Do you need to extend your where clause to include the textbox value to constrain the rowsource?

.RowSource = "Select [NSP]" & _
                        " from Master_Totals_Search_qry " & _
                        " Where [SourceFig]=" & (Me.CbSourceFig) & _
                        " NSP ='" & Me.txtNSP & "'"

We don't know what your query entails.

Update:
Just saw Cronk's response and agree re textbox has no rowsource. I assumed (wrongly) that your rowsource statement was for the combo.

 

[bastanu]

Not sure I get it, but I think you are using the wrong event, you requery in the AfterUpdate of the combo, I would move the code to the Enter event of the combo.

Cheers,
Vlad

 

[Cronk]

If Me.txtNSP is a text box, it doesn't have a requery method nor a rowsource property. If it's a combo, then changing the row source causes a requery.

 

[hllary]

I tried the code, but it's giving me the same error message. On

(Me.CbSourceFig)

The query in the subform is nothing too complicated. It has the text box and combo box in the criteria (OR)

Is there a way to stop the requery?

 

[Pat Hartman]

As Cronk mentioned, if your control is named -- Me.txtNSP -- it should be a textbox which does not have a .RowSource property or a .Requery method. Either don't use prefixes or use them rationally. My combos are named Me.cboSomeFieldName.

I'm pretty sure you don't need to requery at all. Simply setting the .RowSource will requery the combo so setting it to a ZLS would be problematic. What is that all about?

 

[Cronk]

I think we have all got thrown off the track by the recordsource being associated with the text box, at least me. But I think I get it now.

From #1

"I have a text box where the user enters part of a number"

What is the name of that text box? Me.txtNSP?
Where is it located? In the parent form presumably but please confirm.

"the subform show all record that have part of that number (in the text box)"

Assuming Me.txtNSP is in the parent form, you would have in the textbox after update event something like

 Me.YourSubformName.Form.RecordSource = "Select .... FROM ....Where [PartName] like '*" & Me.txt & "*'"

"The combo box show all source of figures associated with that part number"

What is the name of that combo? Me. CbSourceFig? Where is it located? On the parent form?

I presume "that part number" is the selected record in the subform and you want to display in the combo, several values corresponding to that selected part. If so, then the combo rowsource is not based on the text in the text box, but rather the selected subform record.

Accordingly, I'd put the following code in the subform's onCurrent event

me.parent.combo.rowsource = "Select  ..... From .... where PartID =" & nz(me.PartID)

where PartID is the name of your key index field in the subform's recordsource and Me.PartID is the name of the corresponding text box on the subform.

 

[hllary]

Yes, the name of the text box is Me.txtNSP. It is located on the parent form.

The code for the text box is:

Private Sub TxtbxNSN_AfterUpdate()
    Me.SubMaster_Totals_frm.Form.Filter = "[NSP] Like '*" & (Me.TxtbxNSP) & "*'"
    Me.SubMaster_Totals_frm.Form.FilterOn = True
    Me.CboSourceFig.Requery

The name of the combo box is me.CboSourceFig. It is on the parent form.

I think we have all got thrown off the track by the recordsource being associated with the text box, at least me. But I think I get it now.

From #1

What is the name of that text box? Me.txtNSP?
Where is it located? In the parent form presumably but please confirm.


Assuming Me.txtNSP is in the parent form, you would have in the textbox after update event something like

 Me.YourSubformName.Form.RecordSource = "Select .... FROM ....Where [PartName] like '*" & Me.txt & "*'"

What is the name of that combo? Me. CbSourceFig? Where is it located? On the parent form?

I presume "that part number" is the selected record in the subform and you want to display in the combo, several values corresponding to that selected part. If so, then the combo rowsource is not based on the text in the text box, but rather the selected subform record.

Accordingly, I'd put the following code in the subform's onCurrent event

me.parent.combo.rowsource = "Select  ..... From .... where PartID =" & nz(me.PartID)

where PartID is the name of your key index field in the subform's recordsource and Me.PartID is the name of the corresponding text box on the subform.

 

[Cronk]

So is your combo populating now?

 

[hllary]

It's always populated. when I selected an item in the combo box it requerys.

 

[Cronk]

Firstly, does the combo now display what you want? In #1 you said it should be dependent only on information related to the part record selected in your subform.

Secondly, you have not supplied any code for any combo event. What do you want to happen when something is selected in the combo?