Search Button in a Form HELP1

[AC-Dev]

I have created a form which displays data from three joined tables. the form displays the data based on one table using a sort of reference number (ex 1.01, 1.02, 2.01, etc.). The other two tables display the data from the rows that have that reference number attached to is (using a drop down menu which allows for multiple reference number other tables when inputting the data, i.e. one record can have 3 reference numbers of the main table attached to it).


When i search by reference number using my search method, the search returns the correct records for the reference numbers, however it only displays partial data from the other two tables. i have values like "Name?" and "####" returning instead of the actual data. However, if i do not start a search and just scroll through the records, all the data is returned without error.
I am unsure of what to do!
Here is my search code:
Private Sub SearchRecs_Click()

Dim Enter_FOM_Number As Doublet

On Error GoTo Err_cmdSearchRecs_Click

DoCmd.ApplyFilter , "[FOM List.FOM Section] = Enter_FOM_Number"
GoTo cmdSwitchButtons_Click

Exit_cmdSearchRecs_Click:
Exit Sub

Err_cmdSearchRecs_Click:
MsgBox Err.Description
Resume Exit_cmdSearchRecs_Click

cmdSwitchButtons_Click:
Reset.Visible = True
Reset.SetFocus
SearchRecs.Visible = False


End Sub

 

[AC-Dev]

uhm...anybody lol?

 

[VilaRestal]

There's a couple of things about that code that I don't understand

The line:
Dim Enter_FOM_Number As Doublet

What's a doublet class? (I've googled it and the only links are to VBA/Doublet virus)
Did you mean
Dim Enter_FOM_Number As Double
?

The line:
DoCmd.ApplyFilter , "[FOM List.FOM Section] = Enter_FOM_Number"

Shouldn't that be:
DoCmd.ApplyFilter , "[FOM List].[FOM Section] = " & Enter_FOM_Number

and

Enter_FOM_Number has been declared but not assigned a value

Why not:
Me.Filter = "[FOM List].[FOM Section] = " & Enter_FOM_Number
Me.FilterOn = True
?

You say that code does actually apply the filter you want?!?!

 

[AC-Dev]

There's a couple of things about that code that I don't understand

The line:
Dim Enter_FOM_Number As Doublet

What's a doublet class? (I've googled it and the only links are to VBA/Doublet virus)
Did you mean
Dim Enter_FOM_Number As Double
?

The line:
DoCmd.ApplyFilter , "[FOM List.FOM Section] = Enter_FOM_Number"

Shouldn't that be:
DoCmd.ApplyFilter , "[FOM List].[FOM Section] = " & Enter_FOM_Number

and

Enter_FOM_Number has been declared but not assigned a value

Why not:
Me.Filter = "[FOM List].[FOM Section] = " & Enter_FOM_Number
Me.FilterOn = True
?

You say that code does actually apply the filter you want?!?!

sorry, the "doublet" was a typo from when i copied and pasted into the forum.

as for the Enter_FOM_Section, im not quite sure why i did that. it kind of just happened that that is what the box would show when the box prompts the user to enter a value.

i shall try your solution

 

[VilaRestal]

OK but you still need to assign Enter_FOM_Number a value

Maybe
Enter_FOM_Number = InputBox("Enter FOM number to search for")

 

[AC-Dev]

Alright, your search code works as well, but i am getting the same error!!!!

this sucks

 

[VilaRestal]

So all-in-all something like this:

Private Sub SearchRecs_Click()
    On Error GoTo Err_cmdSearchRecs_Click
    Dim s as String
    s = InputBox("Enter FOM number to search for")
    If IsNumeric(s) Then
        Me.Filter = "[FOM List].[FOM Section] = " & CDbl(s) 'Maybe don't need CDbl
        Me.FilterOn = True
        Reset.Visible = True
        Reset.SetFocus
        SearchRecs.Visible = False    
    End If
    Exit Sub
Err_cmdSearchRecs_Click:
    MsgBox Err.Description
    Exit Sub
End Sub

 

[AC-Dev]

Dim strFOM

On Error GoTo Err_cmdSearchRecs_Click

strFOM = InputBox("Please Enter FOM Number", "Search")
Me.Filter = "[FOM List].[FOM Section] = " & strFOM
Me.FilterOn = True
GoTo cmdSwitchButtons_Click


that is the new code (well part of it)

 

[AC-Dev]

I dont believe the search is the problem. The correct number of records return, but only half the values are visible.the date, and one of the numbers returns, but there are 3 fields that do not show up

 

[VilaRestal]

Perhaps post the SQL of the form's recordsource.

If the tables are joined in the usual way you shouldn't get problems filtering them.

 

[AC-Dev]

Perhaps post the SQL of the form's recordsource.

If the tables are joined in the usual way you shouldn't get problems filtering them.

I am not new to development,however i am new to developing in access so i am not sure what the SQL of the forms record source is.

so where would i find it and how much data would that show? i am working with sensitive materials/documents so there is only so much i can show

as for the joined tables, they are standard one-to-many relationships

 

[VilaRestal]

In the form's recordsource property there will either be the name of a query or pure sql (Select * From ... etc.). If it's a query then to see its sql you open in it in design and switch to SQL view.

I can only imagine there is something unusual going on there for fields to stop appearing after a filter is applied

 

[AC-Dev]

alright ill check it out. where do i find that?