Subform search help

[maGemme]

Hi again,

I used to have seperate forms, each would have a basic search function that would allow the user to find a record.

We have now decided to include all of the forms on a single form using tabs and subforms.

The problem that I now face is that whenever I do a search on one of the forms, all of them are updated with random records.

Here's an example of my search code:

Private Sub Search_Click()
'Dim strSearch As String
'Performs the search using value entered into SearchBuildID
'and evaluates this against values in BuildingID
DoCmd.ShowAllRecords
Forms!frmMenu!frmRequests.SetFocus
DoCmd.GoToControl ("Account Number")
DoCmd.FindRecord Me!SearchID

Account_number.SetFocus
SearchID = Account_number.Text
SearchID.SetFocus
strSearch = SearchID.Text
End Sub

How can I have it that it will only return results on a single subform and not all of them?

 

[SteveSchapel]

MaGemme,

So do you mean that you have the control where you enter the search criteria is on the main form? And this control is named SearchID? And this is supposed to find the matching result in the Account Number field in a subform? And the name of the subform is frmRequests?

Just checking that I correctly understand the question...

 

[maGemme]

Your right, I wasn't specific on that.

There is a search function on each subform. SearchID (i know it's not normalized, i'll fix that) is the name of the text box that gathers the information, cmdSearch is the button itself

The main form is frmMenu, the subform is frmRequests and yes, the matching records should be in Account_number (bound text box on the subform).

What I beleive might be the problem is that I used the same naming convention for each for, so earch search text box is named SearchID, each account number (each form has a similar text box) are named Account_number. this may creating confusion but I tried renaming the boxes on 1 form and nothing changed...

Hope that clarifies things a bit

 

[boblarson]

You might also try being more explicit with your code:

[B][COLOR=red]Me.[/COLOR][/B]SearchID = [B][COLOR=red]Me.[/COLOR][/B]Account_number
strSearch = [COLOR=red][B]Me.[/B][/COLOR]SearchID

You don't need to set focus everywhere if you DO NOT USE .TEXT. Use the default - which is .VALUE. You don't need to include .Value (see my code above)

 

[SteveSchapel]

MaGemme,

If there is a separate search box and command button on each subform, then as far as I can see, the only code you would need is:

Private Sub Search_Click()
    Me.Account_number.SetFocus
    DoCmd.FindRecord Me!SearchID
End Sub

 

[maGemme]

MaGemme,

If there is a separate search box and command button on each subform, then as far as I can see, the only code you would need is:

Private Sub Search_Click()
    Me.Account_number.SetFocus
    DoCmd.FindRecord Me!SearchID
End Sub

There you go, that worked like a charm!

Thank you!

Bob, thanx again for the effeciency tips. I will use them!

 

[maGemme]

Ok, new problem

It worked great when I was using it by myself but now I have multiple users using my DB and they all get the following error when using the search :

Run-time error 2137
you can't use find or replace now

I'll head straight to goole to find an answer but thought I'd share and get your input

 

[boblarson]

Ok, new problem

It worked great when I was using it by myself but now I have multiple users using my DB

Split (frontend / backend) with the frontend being a copy on their computers and not using a shared file? That is how it should be if it isn't.

 

[maGemme]

Split (frontend / backend) with the frontend being a copy on their computers and not using a shared file? That is how it should be if it isn't.

Ok it is split but they are using a shared file (station restrictions prevent copying the file to each workstations).

Is there a way around it?

I found another thread that said something about adding
"On Error Resume Next"
before my search code, I was trying this out when I got your response.

 

[boblarson]

Ok it is split but they are using a shared file (station restrictions prevent copying the file to each workstations).

You should work to get that changed. That is a big cause of problems for Access databases. But if you can't, make a folder for each user and give them a copy there (not just a shortcut, a copy of the frontend).

 

[maGemme]

Ok the On error resume next thing didn't work... there's no more error but it doesn't search either.

 

[maGemme]

You should work to get that changed. That is a big cause of problems for Access databases. But if you can't, make a folder for each user and give them a copy there (not just a shortcut, a copy of the frontend).

Ok, I am currently working on something to fix the shared accesss problem and give each user a local copy.

There has to be a way to do get my search function to work without that no? I figure it's a fairly simple matter to find an entry on 1 form without populating all the others while multiple users are in the DB, no?

 

[boblarson]

Ok, I am currently working on something to fix the shared accesss problem and give each user a local copy.

There has to be a way to do get my search function to work without that no? I figure it's a fairly simple matter to find an entry on 1 form without populating all the others while multiple users are in the DB, no?

Something like this:

Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone
 
rst.FindFirst "[FieldNameHere]=" & Me.YourTextBoxNameHere
 
If rst.NoMatch Then
    MsgBox "No Match Found"
Else
    Me.Bookmark = rst.Bookmark
End If
 
rst.Close

Set rst = Nothing

And if your field is text then changed to this:

rst.FindFirst "[FieldNameHere]=" & Chr(34) & Me.YourTextBoxNameHere & Chr(34)

Or if a date:

rst.FindFirst "[FieldNameHere]=#" & Me.YourTextBoxNameHere & "#"

And you can have more than one criteria:

rst.FindFirst "[FieldNameHere]=" & Me.YourTextBoxNameHere & " And [OtherFieldNameHere] <= #" & Me.TextBox2NameHere & "#"

etc.

 

[maGemme]

Ok, sorry for being retarded on this thing. Your code looks promissing but so far I haven't been able to use it successfully.

[FieldNameHere] refers to the field name in the table right?
&
[YourTextBoxNameHere] refers to the text box in which the user enters the information to match in the table?

Here's what I've used: [Account_Number] is a text field in my table (text field required due to special characters) and [txtSearchQst] is the name of the text box in which the user enters the account number to find.

Private Sub cmdSearch_Click()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

'rst.FindFirst "[Account_Number]=" & Me.txtSearchQst
rst.FindFirst "[Account_Number]=" & Chr(34) & Me.txtSearchQst & Chr(34)

If rst.NoMatch Then
MsgBox "No Match Found"
Else
Me.Bookmark = rst.Bookmark
End If

rst.close
Set rst = Nothing


End Sub

I just keep getting the "no match found" msgbox and the number I put in the search text box is copied straight from the table so I know it's right.

 

[boblarson]

Is Account_Number a field in your form's recordsource? And, if so, is it spelled the same as the field in the table?

 

[maGemme]

Yes it is a field in my form's recordsource and yes, it does have the same name as the field in the table.

On my form I use a text box called txtAcntQst that is bound to the Account_Number field in my table. This text box is used for data entry.

 

[maGemme]

Hi again,

Ok, here's where I am, I'll give you my ideas and hopefully you'll be able to help.

So the problem I have as stated in my first post is that each of my subforms have a search function to call back a table entry (to modify, edit, add, etc.). The problem I have is that when I pull an account on 1 form, the other subforms populate with random entries.

Here's what I need to happen; let's say the user is entering data on 1 form but hasn't completed it yet. Something comes up and he has to pull a file from another form. Once the edits to file 2 are done, he can go back to the original subform he was working on and complete the work.

The best fix would have been in the search code itself so that it doesn't populate the other subforms. Since I can't fix that I found a work around.

What I'm thinking is having invisible boxes on the main form and on each subform containing the primary Key number in them. In the "on enter" event of each subform a vba routine would check if there is a key available in the main menu's invisible box, if so goto that record on the subform, else add a new record (this would empty the form filled with the random account).

Hope this makes sense so far...

Here's the code I use, I am still having referening problems (i read the guide you provided me in another post Bob but there is still a problem wiht my code).

Private Sub frmRequests_Enter()
 
If IsNull([txtReqIDMenu]) Then
    DoCmd.GoToControl "frmRequests"
    DoCmd.GoToRecord , , acNewRec
    
Else
     
    Dim rst As DAO.Recordset
         
    Set rst = Me.frmRequests.Form.RecordsetClone
     
    rst.FindFirst "[Me.frmRequests.Form.txtReqID]=" & Me.txtReqIDMenu
    
 
    If rst.NoMatch Then
        MsgBox "No Match Found"
    Else
        Me.Bookmark = rst.Bookmark
    End If
 
    rst.close
    Set rst = Nothing
    
   Me.txtReqIDMenu.value = Null
 
End If
End Sub