Search Box question

[Kill_Switch]

Hello all.

Been scratching my head for awhile now at this. I got most of what I need to do completed, just this search function is giving me a headache. I'm relatively new to M$ Access, and I have the majority of my project done thus far I believe. Can you guys please help me our with the following.

I have an example table as seen in the attachments "vidmartable.gif" Which includes the list of items and their location. As you can see.

I would like to use the following search form "search.gif". The Combo box will look for either the Nato Stock Number or Part Number, and the next text box will be the value to be searched for.

The far right text box, will be where the part is located as per the table above, and after clicking the search button.

 

[JANR]

If one NATO Stocknumber or Partnumer can only be in one location you could use a Dlookup to find the location. If it can be located in several location a querysearch may be required.

Aircode:

Private Search_Click()
Dim sSearch as String
   If Me.[Searchval] = "Nato stock number" Then
 sSearch = Dlookup("Location", "Vidmars", "[NATO Stock number] = '" & Me.[Enter number to search] & "')
   Else
 sSearch = Dlookup("location", " Vidmars", "[Part Number] = '" & Me.[Enter Number to search] & "')
   End If
me.Location = sSearch
End Sub

JR

 

[Kill_Switch]

Only one Nato Stock Number and one Part Number can be in a location.

I right clicked on the submit button (design mode) and clicked build event, and pasted your code, but nothing happened. Am I missing a step?

Also, how does the location get displayed in the "location" text box once it completes its search.

Thank JR for your response.

 

[Kill_Switch]

Okay so I got it to query the vidmar table. So I have the following.

My main page "splash screen" I have a command button. When clicked, calls up a Macro, to open the "Vidmar Search" form.

The vidmar form's record source, is a query. Now for the query I have included all of the values in the vidmar table.

And for the NSN criteria I ahve set to [Enter Nato Stock Number] and in the Criteria "Or" for the Part Number I have [Enter Part Number].

Now so when I want to search the vidmars, a text box appears to enter a NSN, which nothing may be entered, and then a part number text box appears. As stated in the query. Everything works as planned. However can I modify the query to open up both of the box's and choose which number I want to enter, instead of having to hit enter to skip the NSN box to enter a Part Number?


Hope that makes sense.

 

[JANR]

Me.Location = sSearch

This part of the code should populate the location textbox provided it's Named "Location"

The reason to include 2 Dlookups is diffrent syntaxes and fields tosearch in your table, but since you have a setup with only 1 answerbox this was my best ide. I also assume that selection combobox is a Valuelist with only 2 choises "Nato Stock Number" and "Partnumber.

However can I modify the query to open up both of the box's and choose which number I want to enter, instead of having to hit enter to skip the NSN box to enter a Part Number?

A bit unclear. Do you want the combobox to dropdown when the form opens to show it's contents? If so you can use the combobox GotFocus event and use something like this:

Me.SearchCombo.Dropdown

JR

Edit: I found an Error in the dlookup:

"[NATO Stock number] = '" & Me.[Enter number to search] & "'") Missing a quote at the end. Also you might consider wrapping the Dlookup in a NZ function to handle Nulls in the table.

 

[Kill_Switch]

Sorry being unclear, I abandoned my first idea and went with a more "simple" method. So here is my new method, but I would like to combine the 2 screens (3&4)/Parameter Values into one.




Step one, select "search vidmars"


Info box Appears


Then you enter a NSN, or if you only have a PN, press enter


Enter PN



And finally the results




Now is there a way to combine the 2 Parameter Values below, so you only get 1 pop up window?




Sorry JR for being unclear. Although what you explained made sense to me. I'm starting to get the hang of vba code. One day at a time.

 

[JANR]

Now is there a way to combine the 2 Parameter Values below, so you only get 1 pop up window?

Yes it should, if the searchbox is either a NATO number which follows a strickt protocol on how it is entered by this syntax "0000-00-000-0000" and partsnumber dosen't. You could use a Instr function to test for special caracters before using a the Dlookup.


One exemple could be something like this:

Private Sub Search_AfterUpdate()
    Dim sSearch As String
 
    If InStr(Me.Search, "-") Then
        sSearch = Nz(DLookup("location", "vidmars", "[NatoStocknum]='" & Me.Search & "'"), 0)
        Else
        sSearch = Nz(DLookup("location", "vidmars", "[Partnum]='" & Me.Search & "'"), 0)
    End If
    Me.Location = sSearch
End Sub

Here the sub looks for the caracter "-" in the textbox and if it finds one it uses the first Dlookup, if it dosen't it uses the second which is Partnumber. I'v shorten the fieldnames to make it easier for me to write

I'v also added the NZ function to wrap the Dlookup in case there isen't a match in your table, or else you will get an error.

Is this something you are looking for?

JR