Search Form inside of Navigation Form

[accessorater77]

I've added my database as a .zip file, first off.

The search form I've made, FRM_SearchMulti, is from the John Big Booty (Yes, we all hate typing that name out) post on here a few years back.

It works by itself, it but does not work once inside of the Navigation Page Form (Main Page).

Please help, and thanks in advance.

BONUS: Figure out why there is an error every time one types the character "i" in the search bar on the search form.

 

[sneuberg]

You can correct the criteria in QRY_SearchAll by changing them from

Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"

to

Like "*" & [forms]![Main Page]![NavigationSubform].[Form]![SrchText] & "*"

but that problem with typing "i" is a bit of a mystery. This may take me some time. What's the bonus?

 

[sneuberg]

The problem with the character "i" was that the Onchange event was being fired twice. So it was trying to set focus on the listbox when the focus was already there. The reason why the onchange event was being fire twice was from:

1. The letter i being typed in
2. The autocorrect changing i to I

So the solution was to set Allow AutoCorrect to No.

This and the references have been fixed in the attached database.

This implement doesn't work well with spaces but I'll let someone else fix that.

 

[accessorater77]

Brilliant work, thank you so much.

The bonus is that sense of achievement, and knowing you are an Access master, and problem solver extraordinaire.

 

[accessorater77]

This implement doesn't work well with spaces but I'll let someone else fix that.

What did you mean by this, though?

 

[sneuberg]

What did you mean by this, though?

When you try to type in something like "Castle Black" the cursor goes to the beginning of the line when you type the space. So basically this only works for single words.

This thread is getting old. I suggest posting that problem in a new thread.

 

[accessorater77]

Thanks again, sneuberg.

In the actually database I'm working on, not the mockery I posted here, it seems to be searching fine after using a space for multiple words. Not sure what the difference between the two is, but if I do discover it, I can post a new thread with the solution.

 

[Dana_]

You can correct the criteria in QRY_SearchAll by changing them from

Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"

to

Like "*" & [forms]![Main Page]![NavigationSubform].[Form]![SrchText] & "*"

but that problem with typing "i" is a bit of a mystery. This may take me some time. What's the bonus?

Hi Sneuberg could you please help me? I have similar issue as above: I have a Main Form with to buttons once leads to navigation form (Personalhauptseite). In navigation form i have have four another forms and in one of them (Mitarbeiterdaten ändern) i have a search field (txt_Search) where I am looking for a lastname (MA_Nachname). But my code just doesnt work and I dont know what I am doing wrong. Could somebody give me a hint?

[MA_Nachname] Wie "*" & [Formulare]![Personalhauptseite]![Navigationsunterformular].[Formular]![txt_Search] & "*"

 

[arnelgp]

you can use FindRecord, though:

Dim strSearch As String
strSearch = Me.txt_search & ""
If Len(strSearch) Then
    Me.MA_Nachname.SetFocus
    DoCmd.FindRecord strSearch, acAnywhere
    If InStr(Me.last_name, strSearch) = 0 Then
        MsgBox "No matching record found!", vbInformation
    End If
End If
Me.Txt_Search.SetFocus

EDIT: the above code is inside the subform.

 

[Dana_]

you can use FindRecord, though:

Dim strSearch As String
strSearch = Me.txt_search & ""
If Len(strSearch) Then
    Me.MA_Nachname.SetFocus
    DoCmd.FindRecord strSearch, acAnywhere
    If InStr(Me.last_name, strSearch) = 0 Then
        MsgBox "No matching record found!", vbInformation
    End If
End If
Me.Txt_Search.SetFocus

EDIT: the above code is inside the subform.

Thank you sooooooooo much it worked!

 

[Dana_]

Could you maybe help me with another issue please? I have the
same navigation Formular in one of the tabs I have a subform (red) which contains the data in four columns.
I would like to update these entries (green) with help of edit button and
text fields above (see in attachment).

I tried to do it like this, but it doesnt work:

Code:
Private Sub cmdEdit_Click()
mSaved = True
Me.AT_Abteilung = Me.txtAbteilung
Me.FT_Function = Me.txtFunction

End Sub

AT_Abteilung is the third column name (Ausdr 3).

I am new in access, hence have some troubles with vba.

Thank you in advance

 

[arnelgp]

that is a subform (red-rectangle):

Private Sub cmdEdit_Click()
mSaved = True
With Me.subformName.Form
    !AT_Abteilung = Me.txtAbteilung
    !FT_Function = Me.txtFunction
    .Dirty = False
End With

 

[Dana_]

that is a subform (red-rectangle):

Private Sub cmdEdit_Click()
mSaved = True
With Me.subformName.Form
    !AT_Abteilung = Me.txtAbteilung
    !FT_Function = Me.txtFunction
    .Dirty = False
End With

It worked! Thank you

 

[Dana_]

that is a subform (red-rectangle):

Private Sub cmdEdit_Click()
mSaved = True
With Me.subformName.Form
    !AT_Abteilung = Me.txtAbteilung
    !FT_Function = Me.txtFunction
    .Dirty = False
End With

unfortunately, I've encountered some issues with this code. E.g. If I change a selected field, all other
fields with the same value are changed. But I don't want that, I just want the selected field to change.
So I thought I would try to make the change directly in the database using UPDATE. Here is my code.
My subform takes data from three tables (see screenshot) and I would like to be able to change
"Abteilung" and / or "Funktion" using edit button. Unfortunately the update doesn't work yet.
I don't know how to solve this problem? Do you have any suggestions or hints?

Private Sub cmdEdit_Click()
mSaved = True

With Me.MAF_SUB_FORM.Form

CurrentDb.Execute "UPDATE QUERY_MAF_MA_AB_FT SET QUERY_MAF_MA_AB_FT.Abteilung='" & Me.txtAbteilung & _
"', QUERY_MAF_MA_AB_FT.Funktion='" & Me.txtFunktion & _
"' WHERE QUERY_MAF_MA_AB_FT.MA_Vorname=" & Val(!Vorname) And QUERY_MAF_MA_AB_FT.MA_Nachname = " & Val(!Nachname)"
  
Me.MAF_SUB_FORM.Requery

End With
End Sub