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.

 

[Norie]

Sorry, I'm really lost here.

You have something that works?

So what is the actual problem?

Is it because the users have to scroll through the list to find the store they want?

If it is, then they don't.

That's what I'm trying to say, they can just type into the combobox as if it was a textbox and whatever they type can be used for the filter/search.

 

[DAyotte]

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

Still labeled as mentioned.

@Norie: I was just trying to do it without. The combobox was doing little things that was bugging me.

Montez, don't worry about it... I'm not trying to ruin your friday, lol. I'll go with what I have.

Either of you have any idea about my other question regarding the check boxes though? If I have to use radio buttons, that's fine too because if the store is an "A", it can't also be a "B", but it could be null.

 

[Montez659]

D,

You're not ruining my Friday, Lol, I will be thinking about this all weekend anyways!

To answer your other question, I would actually start a new thread on that. I think I know how to help but I am going to be leaving work soon and I rarely check the boards on the weekends. If you start a new thread, somebody will be able to help you out.

And if you are still having problems with the combobox, than I think that there are some underlying issues with the data structure. If the weekend doesn't clear up the cobwebs and give us an answer, then maybe post a snapshot of the relationships screen.

And better than emailing the pics to me, you can set up a free dropbox account and post a public link to the pic, and there will be a reference to it on this board for years as long as you don't delete it.

 

[DAyotte]

D,

You're not ruining my Friday, Lol, I will be thinking about this all weekend anyways!

To answer your other question, I would actually start a new thread on that. I think I know how to help but I am going to be leaving work soon and I rarely check the boards on the weekends. If you start a new thread, somebody will be able to help you out.

I am also leaving work soon... so I will venture back to this on Monday then, and start the new thread. Happy weekend boss.

 

[Norie]

Well I was going to suggest something but since you don't seem to like comboboxes I'm not sure.

Anyway you have 3 options A, B or Null, a single checkbox can only really handle two.

Or do you mean multiple checkboxes, one for A, one for B.

That would be possible if you added some expressions to the query.

Something like this.

CompanyA:[PreviousCompanyField]="A"

CompanyB:[PreviousCompanyField]="B"

You could then use those as the control sources of the checkboxes on the form, but they wouldn't be updateable.

 

[DAyotte]

There is no query however. Is there another way to attach that particular code?

I'm going to go with the combo box. It's working. I though it would have been a simple issue, not an all day venture. Not a huge deal.

After all this... the beer is going to taste extra good tonight.

 

[Norie]

What do you mean there's no query?

You can use queries as the record sources for forms as well as tables, and they give you a lot more flexibility.

I actually just created this small, continous form.

Based on this data:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblStores</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>StoreName</TH><TH bgColor=#c0c0c0 borderColor=#000000>StoreID</TH><TH bgColor=#c0c0c0 borderColor=#000000>PreviousCo</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1>Spar</TD><TD borderColor=#eeece1 align=right>1</TD><TD borderColor=#eeece1>A</TD></TR><TR vAlign=top><TD borderColor=#eeece1>Debenhams</TD><TD borderColor=#eeece1 align=right>2</TD><TD borderColor=#eeece1>
</TD></TR><TR vAlign=top><TD borderColor=#eeece1>Walmart</TD><TD borderColor=#eeece1 align=right>3</TD><TD borderColor=#eeece1>B</TD></TR><TR vAlign=top><TD borderColor=#eeece1>7-11</TD><TD borderColor=#eeece1 align=right>4</TD><TD borderColor=#eeece1>A</TD></TR><TR vAlign=top><TD borderColor=#eeece1>Papas</TD><TD borderColor=#eeece1 align=right>5</TD><TD borderColor=#eeece1>A</TD></TR></TBODY><TFOOT></TFOOT></TABLE>


The combobox in the top right has the following code allowing you to search by partial name and store ID.

Private Sub cmbStore_Change()
    Dim strFilter As String
    If IsNumeric(Me.cmbStore.Text) Then
    
        strFilter = "StoreID=" & Me.cmbStore.Text
    Else
        strFilter = "StoreName Like '*" & Me.cmbStore.Text & "*'"
    End If
    
    Me.Filter = strFilter
    
    Me.FilterOn = True
End Sub

That code could be added to the Search button instead.

Is this anything like what you are trying to do?

By the way you could use a form like this to search for the store then have a button that when pressed opens another form with all the
fields for the store.

 

[DAyotte]

That is kind of like what I am trying to do. I just have one store at a time coming up though, instead of all of them. I'm sure it would be pretty close.

 

[DAyotte]

I think the way that my combo box is working for the form is good, but how are you getting those check boxes to work?

 

[Norie]

Using the expressions I mentioned in the previous posts.

By the way, not sure what you mean by one store coming up at a time.

Is that what you want to do?

The form I designed and posted the image of is a continuous form which would basically be a list of stores.

The user could even just scroll through it to find the store they want, or use Access' built-in filters.