Form population via button

[DAyotte]

Ok, I did a brief search through the forum (flame me if necessary), and although I did find something similar to what I'm looking for... it's not exactly what I need.

Using Access 07, I've put together a table of data with about... 7400 records - each one has a specific ID number.

I've then designed a form with all text boxes bound to the various columns of data. I want the user to be able to type in the ID they are requesting, hit the search button and BOOM, all the info is there. Everything I'm coming across is saying to use combo boxes, but with as many records as I have... that wouldn't be a viable option.

Please Assist.

 

[Montez659]

Yikes! Yeah, you have been deleting. Undoing the filter is as simple as setting it to nothing:

Me.Filter = ""
DoCmd.RunCommand acCmdApplyFilterSort

Me.Text_StoreNumber.Value = ""

This leads me to believe that your filter should look like this:

Me.Filter = "Text_StoreNumber = " & Me.Search.Value
DoCmd.RunCommand acCmdApplyFilterSort

And I am not 100% sure, but "Search" might be a reserved word. Might want to change it to SearchMe or something. Norie, do you know if it is reserved?

 

[DAyotte]

That took care of the issue with the clear - thank you much on that.

The other one is still giving an error in VBA, however. I renamed it to it's default, which was command74.

The error is "Compile Error: Method or data member not found."
The part that is shown is the "search." portion of the code you gave me

Me.Filter = "Text_StoreNumber = " & Me.Search.Value
DoCmd.RunCommand acCmdApplyFilterSort

I tried changing that to Me.Command74.value - as I was unsure if search was an expression used in the code, or if you were telling it to use that button, but it netted the same result anyway. As you can see, i'm not too familiar with VBA, so I apologize in advance.

 

[Montez659]

Sorry, I am a moron!
In here

Me.Filter = "Text_StoreNumber = " & Me.Search.Value

where it says "Search" should be the name of the textbox where you enter the value that you are searching for. I mistakingly named it the name of the button itself, which has no value. So change "Search" to

Me.Filter = "Text_StoreNumber = " & Me.theNameOfTheTextboxHere.Value

That should do it.

 

[DAyotte]

well... I'm not getting the error that I was, but it's still not populating anything. I really appreciate the effort you have put in, but I can just use the combo box. I've been able to format it so it looked alright. However... I do have another idea that I am lost on.

Certain stores on this list were obtained in a merger. In my table I have a column that is blank if it wasn't, but if it was, it contains the company it was previously. There is 2 possibilities, we'll call "A" and "B" or null if it wasn't a merger. Is there a way to get this to put a check in box 1 if "A", a check in box 2 if "B" and no check if null?

I'm going above and beyond my call of duty here, because if I can create this form and get it to function, it'll save our dept a crap-load of time.

 

[Norie]

Can you tell us more about the table structure?

Also have tried creating a split form as Montez suggested.

If you are using Access 2007 or later then Access should do all the work for you.

That might not give you exactly what you want, wizards etc never do, but it will give you something to work with.

Montez

Why do you use DoCmd to apply the filter?

Is it more efficient?

I usually just use the FilterOn property/method.

PS Might be wrong with the name there but there is something you can use to filter a form.

 

[Montez659]

Montez

Why do you use DoCmd to apply the filter?

Is it more efficient?

I usually just use the FilterOn property/method.

No, you got it right, it is the FilterOn property. I just forgot about it!

Dayotte,

I agree with Norie, we might need to see a little more info about the table structure, and also post the completed code too. Use code tags (you can manually insert these with [ code] and [/ code] without the spaces. This retains formatting for easier to read code.).

As we have presented the code, it should work so don't give up yet. Set your sights on the goal and that is ease of use for yourself, and we will get there eventually.

 

[DAyotte]

I am very grateful for your persistence.

Before I go any further, what info would you like on my table?

 

[Montez659]

Just had a thought. What is the record source of the form? You will find that in the properties tab under Data. Is it a table name, or does it begin with SELECT? If SELECT, copy that property and paste here with the codes tags.

 

[DAyotte]

it's a table name, no select:

Tbl_Store Data

 

[Montez659]

What is the name of the textbox that we are trying to pull info from?