Creating a Search Form

[hd5al]

Ladies/Gentlemen,

As a newbie, I've obviously come to the internet to try and find the answer to my problems. I've been trying to crack this by trial and error, but my VB code just isn't good enough. I know what I want to say, but Access isn't having any of it!

Basically, I'm trying to create a search form, in which the user can select a field to search, and then type the search criteria, hit a button and away we go. There are only 8 field and 1 table. I have the search fields listed in a combo box, all assigned a unique identifying number, hidden from view, but nonetheless in the combo box.

The code I want to write sounds, in my head, like:
IIf([searchfield].Number = 1, RunMacro "searchdate")
IIf([searchfield].Number = 2, RunMacro "searchtype")

Does any guru know whether I'm going about this the wrong way? Tried as many different variations as I can but cannot seem to get the button to access the number assigned in the combo box.

I'm entering the information into the "on click" box on the properties of the button command, but I've also got a feeling that this is the wrong place to put code that is a bit more complex. Can anyone give any pointers?

Many thanks,

Dave

 

[pbaldy]

You are correct that you don't want this directly in the "on click" properties line. Delete it from there, and while still in that line click on the ellipsis (...) to the far right. If it asks, specify Code Builder. It should bring you into the VBA editor, and it should have built the "frame" around your code:

Private Sub ButtonName_Click()

End Sub

Between those 2 lines is where your code will go. One way you refer to the properties of a combo is:

Me.ComboName.Value

You want to look at If/Then/Else or Select/Case in VBA help to organize your code, and by the look of it Select/Case would be better here. You'll also want to look at the various DoCmd commands, one of which will run your macros.

 

[hd5al]

Thanks for the reply!

This is really frustrating me, because I know the code is going to end up being really simple, I'm just getting really confused as to how I actually reference the combo box.

This is how I see it, from my head and from looking at your previous reply:

IIf(Me.SearchField.Number = 1 then DoCmd.RunMacro "SearchDate", Is Null)

I don't know where I'm getting confused

Grr, may try and make it a bit more long winded, simpler coding! I'm an engineer, my knowledge of programming is worryingly lax!

Dave

 

[pbaldy]

Well, I didn't say to use an IIf() function, I said If/Then/Else or Select/Case. I also didn't say to reference the combo with ".Number" at the end, I said ".Value":

If Me.SearchField.Value = 1 Then
  DoCmd.RunMacro "SearchDate"
End If