Search Form inside of Navigation Form (1 Viewer)

accessorater77

Registered User.
Local time
Today, 14:11
Joined
May 26, 2016
Messages
13
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.
 

Attachments

  • Database4.zip
    45.9 KB · Views: 306

sneuberg

AWF VIP
Local time
Today, 14:11
Joined
Oct 17, 2014
Messages
3,506
You can correct the criteria in QRY_SearchAll by changing them from
Code:
Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"

to
Code:
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?
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 14:11
Joined
Oct 17, 2014
Messages
3,506
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.
 

Attachments

  • Database4Modified.accdb
    704 KB · Views: 300

accessorater77

Registered User.
Local time
Today, 14:11
Joined
May 26, 2016
Messages
13
Brilliant work, thank you so much.

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

sneuberg

AWF VIP
Local time
Today, 14:11
Joined
Oct 17, 2014
Messages
3,506
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

Registered User.
Local time
Today, 14:11
Joined
May 26, 2016
Messages
13
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_

New member
Local time
Today, 22:11
Joined
Nov 23, 2021
Messages
18
You can correct the criteria in QRY_SearchAll by changing them from
Code:
Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"

to
Code:
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?

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

Attachments

  • searchField.PNG
    searchField.PNG
    59.4 KB · Views: 202

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:11
Joined
May 7, 2009
Messages
19,169
you can use FindRecord, though:
Code:
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_

New member
Local time
Today, 22:11
Joined
Nov 23, 2021
Messages
18
you can use FindRecord, though:
Code:
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_

New member
Local time
Today, 22:11
Joined
Nov 23, 2021
Messages
18
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
form.PNG


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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:11
Joined
May 7, 2009
Messages
19,169
that is a subform (red-rectangle):
Code:
Private Sub cmdEdit_Click()
mSaved = True
With Me.subformName.Form
    !AT_Abteilung = Me.txtAbteilung
    !FT_Function = Me.txtFunction
    .Dirty = False
End With
 

Dana_

New member
Local time
Today, 22:11
Joined
Nov 23, 2021
Messages
18
that is a subform (red-rectangle):
Code:
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_

New member
Local time
Today, 22:11
Joined
Nov 23, 2021
Messages
18
that is a subform (red-rectangle):
Code:
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?

Code:
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
 

Attachments

  • db.PNG
    db.PNG
    39 KB · Views: 164

Users who are viewing this thread

Top Bottom