CODEWITHSUNDEEP
ms-accesssearch
imprz
imprz

Reputation: 29

How to refer to a table of values in a subform in Microsoft Access

I'm working on a searching function for my database. I've created a test DB to figure things out and I've gotten stuck. When I'm on the same form searching within a range is quite straight forward and I accomplished it using the following VBA code:

Private Sub Command12_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.Text8) Then
    strWhere = strWhere & "( [testNUm] >= " & Me.Text8 & ") AND "
End If

If Not IsNull(Me.Text10) Then
    strWhere = strWhere & "( [testNUm] < " & Me.Text10 & ") AND "
End If

lngLen = Len(strWhere) - 5

If lngLen <= 0 Then
    MsgBox "no criteria", vbInformation, "Nothing to do"
Else
    strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If

End Sub

The above code seems to work fine, however, when I try to place my form in a tabbed pane view and have it as a subform to another form, I am unable to properly access the table of that subform. I've tried using various methods shown here http://access.mvps.org/access/forms/frm0031.htm but I can't seem to get it to do anything more than refer to the particular textbox in the subform. 

Private Sub Command24_Click()

Dim strWhert As String
Dim lngLeng As Long

MsgBox Forms!test2!Child16.Form.testNUm, vbInformation, "wtf"

If Not IsNull(Me.Text20) Then
    strWhert = strWhert & "( Forms!test2!Child16.Form.[testNUm] >= " & Me.Text20 & ") AND "
End If

If Not IsNull(Me.Text22) Then
    strWhert = strWhert & "( Forms!test2!Child16.Form.[testNUm] < " & Me.Text22 & ") AND "
End If

lngLeng = Len(strWhert) - 5

If lngLeng <= 0 Then
    MsgBox "no criteria", vbInformation, "Nothing to do"
Else
    strWhert = Left$(strWhert, lngLeng)

Forms!test2!Child16.Form.Filter = strWhert
Forms!test2!Child16.Form.FilterOn = True

End If

End Sub

I'm pretty unskilled at VBA programming, and I've been left with a task of creating a DB from scratch in 2 weeks. Thanks in advance for any assistance you can provide.

Upvotes: 0

Views: 995

Answers (1)

HansUp
HansUp

Reputation: 97101

I don't understand which form contains Command24. You can find out by temporarily changing Command24_Click.

Private Sub Command24_Click()
    Debug.Print "'Me' refers to " & Me.Name
    Stop
End Sub

The Stop statement will put you in break mode. Then you can use the Immediate Window to explore object path variations until you find the one which refers to the correct form.

However, given your particular situation (little VBA experience and a tight deadline), I will encourage you to switch to a simpler form design. Ask yourself whether the search form absolutely must be embedded in another form on a page in a tab control.

Edit: The comment about naming scheme was spot on, IMO. Apparently you agree, and intend to change to meaningful names later. If you do it now, you can make your trouble-shooting easier. In particular, be reluctant to hardwire the name of a form like this:

Forms!test2

"test2" is a poor choice for a form name; surely you will be changing that one. So, for now use "Me" to refer to the form which contains your command button. Then your code will not break when you rename the form. That's one less detail to fiddle with later.

Still unsure how well I understand your form details, but I suggest you go into break mode after setting the subform's filter and check that you got what you wanted. In the Immediate Window, try variations of this:

? Me.Child16.Form.FilterOn

I said variations because I'm skeptical about whether I got the object path correct. Adjust it as needed.

Edit2: After your code finishes constructing strWhere, print it to the Immediate Window so you can confirm it contains what you expect (or display it in a MsgBox if you prefer).

Debug.Print "strWhere: " & strWhere

Edit3: You're building your filter expression with statements like this:

"( Forms!test2!Child16.Form.[testNUm] < " & Me.Text22

I would use the name of a field in the form's record source on the left side of the < character.

"([SomeNumberField] < " & Me.Text22

Upvotes: 1

Related Questions