Control sub table with button (1 Viewer)

[Heidestrand]

Hello community,

I've a question: Is it possible to control an embedded table within a form by the use of a button? I created a form that looks like this:

When the user enters a SAP number into the text field and clicks the go button some code behind it looks up the SAP number in a table and displays the associated dates. Then the user can click on a date and after that
the table embedded in this form should display the SAP number and the date.

So is this possible to control the content of this table after the user chose a date?

Best regards,
Heide

 

[sneuberg]

You could filter the subform with code something like the following in the Go button's click event

Dim strFilter As String
    
strFilter = "[SAP Number Field Name] =  '" & Me.SAPNumberTextBoxName & "'"  'If SAP Number is text
Forms!themainformname!thesubformname.Form.Filter = strFilter
Forms!themainformname!thesubformname.Form.FilterOn = True

This is very generic as I don't know the names of any of your controls.

The rest could be done in the on_click event of the data field in the subform

 

[Heidestrand]

Hey sneuberg,

thank you for the start I adjusted your code according to my needs:

strFilter = "[SAPNr] =  '" & dblSAPNr & "'"
Forms!frmSnippet!tblFinalOrderSubForm.Form.Filter = strFilter
Forms!frmSnippet!tblFinalOrderSubForm.Form.FilterOn = True

The only problem is that Access returns a run-time error 3464: data type mismatch in criteria expression. The original table that this sub table refers to or is a part of has the data type "double" for storing SAP numbers.
Thus I converted the read SAP number from the data field into double:
strSAP = txtSAPNr
dblSAPNr = CDbl(strSAP)
But Access still returns the error..

 

[sneuberg]

Probably SAPNr is numeric in which case you don't want the single quotes. Try

strFilter = "[SAPNr] = " & Me.txtSAPNr
Forms!frmSnippet!tblFinalOrderSubForm.Form.Filter = strFilter
Forms!frmSnippet!tblFinalOrderSubForm.Form.FilterOn = True

 

[sneuberg]

I've seen code where you could have the subform update as the user types in the SAP Number. That way they might be able to get to the SAP by entering just a few digits. If you are interested I'll see if I can find that code.

 

[Heidestrand]

Ah perfect, now it works! This method works for me, but thank you for offering that other version to me

One last question: If I want to add a second filter criteria can I add this with:

strFilter = "[SAPNr] = " & Me.txtSAPNr & "[AngelegtAm] = " & Me.txtDate ?

 

[Grumm]

You still need the 'AND' or 'OR' keyword between the criteria :
strFilter = "[SAPNr] = " & Me.txtSAPNr & " AND [AngelegtAm] = " & Me.txtDate

 

[sneuberg]

If AngelegtAm is a date then

strFilter = "[SAPNr] = " & Me.txtSAPNr & " AND [AngelegtAm] = #" & Me.txtDate & "#"

If AngelegtAm is text then

strFilter = "[SAPNr] = " & Me.txtSAPNr  & " AND [AngelegtAm] = '" & Me.txtDate & "'"

but if AngelegtAm is a date but a text field in the table I recommend you change it to an date type. If you need help converting let me know.

 

[Heidestrand]

@sneuberg:

Regarding the date, I do it like this:

strDate = Format(strDateRaw, "\#yyyy\-mm\-dd\#")
strFilter = "[SAPNr] = " & Me.txtSAPNr & " AND [AngelegtAm] = " & strDate

This works If I've further questions I will turn to you