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]

This should help you with what you need.

http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[DAyotte]

That's a start... but instead of running a query and showing that alone, I would like it to populate back into the form.

 

[Montez659]

Sorry, didn't read the post well.

I am not sure of what the structure of your form looks like, but you would probably need a split form (not necessarily, but might look better) and in the top, include a text box and a command button. In the the textbox, they would enter the record ID that they were searching for, and then the command button would set the filter proerty of the form. This would be behind the On Click event of the command button

Me.Filter = "recordID = " & Me.yourTextBox.Value
DoCmd.RunCommand acCmdApplyFilterSort

 

[DAyotte]

That's closer...

My form has 21 fields. The first is Store number, and on the side of that - Search and Clear buttons. I want to be able to type in the store number, hit search, and the other 20 fields populate with all the information based on the record in my table for that store. Right now, the clear button works, and I put in that last code you gave me, changing "RecordID" to "StoreNumber", and "Me.yourTextBox.Value" to "Me.Text_StoreNumber.Value" (which is their respective names). However, when I type in the first number, a text box prompts me for the store number again. I typed it in, and it brought up the information for a different store. (ex: I typed in 326, and it brought up the info for store 462; which is the 326th record in the file.) I don't have a primary key.

 

[DAyotte]

furthermore, it only worked the first time. I can clear the record, and it won't search another.

 

[Norie]

What fields do you have in the table?

Do you have an autonumber field?

Which field has the ID you mention in the first post?

 

[Montez659]

Ahh, when you said ID in the first post, I assumed you meant the record ID. You said that you changed the recordID to StoreNumber. Is StoreNumber the name of the bound field in the form, or the name of the field in the record? This might be causing that parameter box to pop up. For instance, if the field in the record is named StoreNumber, but the field in the form is Store_Number, Store_Number would need to be put into the code, so double-check that.

Also, as far as the clear that you have, post the code that you have behind that so I can take a look.

 

[DAyotte]

What fields do you have in the table?

Do you have an autonumber field?

Which field has the ID you mention in the first post?

I do not.

 

[DAyotte]

Ahh, when you said ID in the first post, I assumed you meant the record ID. You said that you changed the recordID to StoreNumber. Is StoreNumber the name of the bound field in the form, or the name of the field in the record? This might be causing that parameter box to pop up. For instance, if the field in the record is named StoreNumber, but the field in the form is Store_Number, Store_Number would need to be put into the code, so double-check that.

Also, as far as the clear that you have, post the code that you have behind that so I can take a look.

Yea, and I just realized my clear is working a delete. Awesome...
I was being vague to make sure I didn't breach company policy. But I've decided that I really need to get this working, lol. As far as bound fields... there are a lot of those in my form. I was under the impression that once the store number is populated, they would need to be bound in order to pull the information.

My data base consists of the Table: Tbl_Store Data, and a Form: Frm_Store Data Find. My form contains a text box for every field in the table, and they are all bound. Most locked, but some are not in the case of a district re-alignment or what-have you. Obviously, the form is based on the table. I really think that the problem lies in the program of the button.

Just for kicks, I've put a combo box in there and I was able to get it to work that way. I know I'm a pain, but I really my idea to work without it.

Here is the code behind the "search button":
Private Sub Search_Click()
Me.Filter = "StoreNumber" = " & Me.Text_StoreNumber.Value"
DoCmd.RunCommand acCmdApplyFilterSort
End Sub

And here is the "Clear":
Private Sub Clear_Click()
Me.Text_StoreNumber.Value = ""
Me.Text_Address.Value = ""
Me.Text_City.Value = ""
Me.Text_State.Value = ""
Me.Text_phone.Value = ""
Me.Text_Fax.Value = ""
Me.Text_eFax.Value = ""
Me.Text_NPI.Value = ""
Me.Text_NABP.Value = ""
Me.Text_TAXid.Value = ""
Me.Text_Area.Value = ""
Me.Text_Region.Value = ""
Me.Text_Zone.Value = ""
Me.Text_District.Value = ""
Me.Text_Update.Value = ""
Me.Text_CloseDate.Value = ""
Me.Text_AudAsst.Value = ""
Me.Text_DMname.Value = ""
Me.Text_DMphone.Value = ""
Me.Text_DMemail.Value = ""
Me.Text_RXSname.Value = ""
Me.Text_RXSphone.Value = ""
Me.Text_RXSemail.Value = ""
Me.Text_Comment.Value = ""
End Sub