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.

 

[DAyotte]

Yea, I meant binding - sorry.

"It's Friday, give me a break, "

 

[DAyotte]

Still not working. It brings up a dialogue box asking me for the store number. I type in "326" (without quotes) and it pulls all the information for store 3. I clear and try again, store 5151, and when I click Search nothing happens.

It's only pulling info on that first one, but the wrong info.

 

[DAyotte]

and I C/P'ed the code right from your post.

 

[Montez659]

Hmmm. I am stumped.

Alright then...shoot me a screen shot in design view to my email. I will send that to you PM.

 

[Norie]

A combobox is basically a textbox with an added dropdown list.

If you added an unbound combobox with a query that returns all the store IDs in ascending(or descending) order you could then give the user the option of
either typing the ID in or selecting it from the list.

For example I created a simple form based on this small table.

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblSalesPeople</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>SalesPerson</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1 align=right>1</TD><TD borderColor=#eeece1>Mike</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>2</TD><TD borderColor=#eeece1>Jennifer</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>3</TD><TD borderColor=#eeece1>Frances</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>4</TD><TD borderColor=#eeece1>Robert</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>5</TD><TD borderColor=#eeece1>Stuart</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>6</TD><TD borderColor=#eeece1>Mary</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I added a combobox (Combo5) and button (Command7) in the header and added this code:

Private Sub Command7_Click()
Dim strFilter As String
 
    If IsNumeric(Me.Combo5.Value) Then
    
        strFilter = "ID=" & Me.Combo5.Value
    Else
        strFilter = "SalesPerson = '" & Me.Combo5.Value & "'"
    End If
 
    Me.Filter = strFilter
    
    Me.FilterOn = True
    
End Sub

I set the row source of the combobox to list only show the names but I could enter either the name or ID to filter.

 

[DAyotte]

sent. let me know if you need anything else.

 

[DAyotte]

A combobox is basically a textbox with an added dropdown list.

If you added an unbound combobox with a query that returns all the store IDs in ascending(or descending) order you could then give the user the option of
either typing the ID in or selecting it from the list.

For example I created a simple form based on this small table.

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblSalesPeople</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>SalesPerson</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1 align=right>1</TD><TD borderColor=#eeece1>Mike</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>2</TD><TD borderColor=#eeece1>Jennifer</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>3</TD><TD borderColor=#eeece1>Frances</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>4</TD><TD borderColor=#eeece1>Robert</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>5</TD><TD borderColor=#eeece1>Stuart</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>6</TD><TD borderColor=#eeece1>Mary</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I added a combobox (Combo5) and button (Command7) in the header and added this code:

Private Sub Command7_Click()
Dim strFilter As String
 
    If IsNumeric(Me.Combo5.Value) Then
    
        strFilter = "ID=" & Me.Combo5.Value
    Else
        strFilter = "SalesPerson = '" & Me.Combo5.Value & "'"
    End If
 
    Me.Filter = strFilter
    
    Me.FilterOn = True
    
End Sub

I set the row source of the combobox to list only show the names but I could enter either the name or ID to filter.

Norie, I have a version with a functional combobox. I will use your suggestion if we can't get it to work without. I just had that hope, y'know?

 

[Montez659]

So the Textbox that says STORE, did you change the name on that, or is it still Text_StoreNumber.

And then even then, if you had to manually input it in the parameter box, it should have returned the correct record.

In your table, is STORE a text field or number field?

And go ahead and work up a copy with what Norie is saying, because if that works, then there is no reason why this shouldn't work...all we would be doing is changing the control type from a combobox to textbox, although if his works and you like it we could stick with that one.

This has really got me scratching my brain. It almost seems like there is something else that is filtering these records for you to not get the correct record.

 

[DAyotte]

The store field in the table is a number field. Could that be the issue?

 

[Montez659]

Doubtful, but something to try. Don't change anything with that, but let me know on the other question.