New user question

[eTom]

After fixing it to ItemNumberID, it lists every product and the total of Quantities. Perfect.

I have a field in "Products" called Obsolete that is a Yes/No. I check it off if the product is obsolete and no longer used. I've used that in my reports to only list data that is relevant to current products.

How exactly do I check this in the SQL view to only show products who's Obsolete field are "No"?

I was trying WHERE Products.Obsolete = "No" to no avail. It just pops up a dialog box asking me to type in. If I put "No" it lists all the totals, anything else and it's a blank worksheet.

 

[Scooterbug]

Add the products table to the query and link it to the IncidentDetail table. Add the Obsolete field from the products table and set the criteria to No. That should do the trick.

 

[eTom]

Add the products table to the query and link it to the IncidentDetail table. Add the Obsolete field from the products table and set the criteria to No. That should do the trick.

Oh. That was simple. I was trying to do it all with SQL. I guess they created a GUI for a reason.

 

[Scooterbug]

Good table structure + GUI query builder = Hassle Free Development

 

[eTom]

Alright, here's another two questions for you guys!

I've started splitting more and more info into separate tables like I should have in the beginning. Example: tblStore had a column for Country, but it was just typed in. I've now created tblCountry, and changed that column to CountryID, which is related to the primary key CountryID in tblCountry. This means that I have four or five tables all linked to tableCountry.CountryID. (tblIncident, tblStores, tblDistributors, etc.) Is this a good thing? The relationship window is getting very complicated!

Second, I'm going to start adapting my forms now that everything is linked a little more thoroughly. Can I script it so that when a user picks a store from the ComboBox (populated from tblStore, of course), it will automatically fill in the ComboBox for Country and State, based on the tblStore.CountryID etc? It's all linked anyway, right?

If that's possible, can I specify that it does that only if those two fields haven't been input already? (Perhaps the user has to file the Incident as being from the USA, even though the product was purchase at a Canadian store, for example.)

I figured I'd get your opinion on whether that kind of thing is possible before I start researching some of the programming aspects.

Thanks!

 

[Scooterbug]

I've started splitting more and more info into separate tables like I should have in the beginning. Example: tblStore had a column for Country, but it was just typed in. I've now created tblCountry, and changed that column to CountryID, which is related to the primary key CountryID in tblCountry. This means that I have four or five tables all linked to tableCountry.CountryID. (tblIncident, tblStores, tblDistributors, etc.) Is this a good thing? The relationship window is getting very complicated!

You can establish a relationship as many times as you want. Access doesn't care

Second, I'm going to start adapting my forms now that everything is linked a little more thoroughly. Can I script it so that when a user picks a store from the ComboBox (populated from tblStore, of course), it will automatically fill in the ComboBox for Country and State, based on the tblStore.CountryID etc? It's all linked anyway, right?

You shouldn't need to re-store the Country and State if of the store if that information is stored in the tblStores. You can populate some unbound text boxes to show the data if you want. You should only store the Country and State separately if that data is going to be different from the Country and State stored in the tblStore. (See below) To display the country and State in text boxes, all you need to do is include that data in the combo box for the Stores. You dont have to display them, but you can reference them. Something like this:

Private Sub cboStore_After_Update()
me.txtStoreCountry = me.cboStore.column(2)
me.txtStoreState = me.cboStore.column(3)
 
end sub

Remember, that the numbering for columns for both combo boxes and list boxes start at 0.

If that's possible, can I specify that it does that only if those two fields haven't been input already? (Perhaps the user has to file the Incident as being from the USA, even though the product was purchase at a Canadian store, for example.)
[/quote]

If there is a possibility that where the Incident is from and where the purchase place was, then you should have a field for each. (IncidentFiledFrom, ProductPurchasedFrom).

I figured I'd get your opinion on whether that kind of thing is possible before I start researching some of the programming aspects.

Thanks![/quote]

 

[eTom]

You can establish a relationship as many times as you want. Access doesn't care


You shouldn't need to re-store the Country and State if of the store if that information is stored in the tblStores. You can populate some unbound text boxes to show the data if you want. You should only store the Country and State separately if that data is going to be different from the Country and State stored in the tblStore. (See below) To display the country and State in text boxes, all you need to do is include that data in the combo box for the Stores. You dont have to display them, but you can reference them. Something like this:

[/QUOTE]

Yeah, that's exactly why I'm doing it. Nine times out of ten the complaint will come from the same state as the product is purchased, but every now and again you'll get someone who takes it over state lines and then complains. Even more rarely we get someone who is travelling abroad with a complaint about a bag from a different country than the one they are in.

I figure that the user can pick the store from a drop down, and then the country / state will fill in. Although, now that I think about it, there is a very very long list of stores. Perhaps it'd be easier to have the user choose country, then state, then store.

Can I have the store combo-box filtered by the country they've already picked? Or better yet, filtered by the state combo-box which is itself filtered by the country combo-box?

 

[Scooterbug]

Can I have the store combo-box filtered by the country they've already picked? Or better yet, filtered by the state combo-box which is itself filtered by the country combo-box?

Yup, it's what is called Cascading Combo Boxes. Basically, you use the choice from one combo box as criteria for the next one. So, in your case, you could do something like this:

First Combo Box: Select * from tblCountry
Second Combo Box: Select * from tblStore WHERE countryID = Forms!FromName!FirstComboBoxName

 

[eTom]

Yup, it's what is called Cascading Combo Boxes. Basically, you use the choice from one combo box as criteria for the next one. So, in your case, you could do something like this:

First Combo Box: Select * from tblCountry
Second Combo Box: Select * from tblStore WHERE countryID = Forms!FromName!FirstComboBoxName

Ah! Again with the SQL. I think I'm starting to like this SQL stuff!

 

[eTom]

First Combo Box: Select * from tblCountry
Second Combo Box: Select * from tblStore WHERE countryID = Forms!FromName!FirstComboBoxName

Worked like a charm, thanks! Now I have a problem with population. If I try to choose a Province/State first, there is nothing in the list, since there is nothing in the Form's CountryID combo-box to match to. If I go choose a country, and then try to pick a province/state, it's still listing nothing because the combo-box was populated before a country was chosen.

Is there a way to repopulate the province/state combo-box after choosing a country? Like an "on select" event or something?

 

[Scooterbug]

To repopulate a combo box, you need to do a .requery

Would look like this (Put on either the On Change or After Update event of the combo box):

me.ComboBoxName.requery

That should do it

 

[eTom]

To repopulate a combo box, you need to do a .requery

Would look like this (Put on either the On Change or After Update event of the combo box):

me.ComboBoxName.requery

That should do it

I don't think I'm putting this in the right place. I imagine it goes on the first combo-box. The one that if I change it, I need the second to be requeried. If I select a country now, "Access can't find the object 'me.'"

I had put that line in the "On Change" property on the Country combo-box's property sheet. Is that where it's supposed to go?

 

[Scooterbug]

Yes, the code would go on the On Change event of the first combo box. You would requery the second combo box.

Make sure you are putting the code in the code builder and not just the field for the On Change Property. (Click on the Properties, then the Event, then the button with the 3 dots on it.) A window will show up with three options. Choose the Code Builder.

 

[eTom]

Man, you're quick.

Thanks, that's exactly what it was!

 

[Mr. B]

eTom,

Although Scooterbug, is correct about the code and even correct that it will work as described, I would place the code in the "After Update" event of the combo box, not in the "On Change" event.

The reason is that the "On Change" event will fire everytime any change is made even to one character in the combo box. If the user started to type in the combo box, the "On Change" event would fire, requering the next combo box every time. The "After Update" event only fires when the data entry has been completed. This would occur when the user made a selection from the first combo box or when they typed in a value and then pressed the "Enter" or "Tab" key to move on to the next control.

This might seem like a very little thing but if you have a dataset that takes more than a split second to load into the second combo box, it can make a big difference in speed.

Just my thoughts.

 

[eTom]

eTom,

Although Scooterbug, is correct about the code and even correct that it will work as described, I would place the code in the "After Update" event of the combo box, not in the "On Change" event.

The reason is that the "On Change" event will fire everytime any change is made even to one character in the combo box. If the user started to type in the combo box, the "On Change" event would fire, requering the next combo box every time. The "After Update" event only fires when the data entry has been completed. This would occur when the user made a selection from the first combo box or when they typed in a value and then pressed the "Enter" or "Tab" key to move on to the next control.

This might seem like a very little thing but if you have a dataset that takes more than a split second to load into the second combo box, it can make a big difference in speed.

Just my thoughts.

Ah, good point. While I've been testing it by using the mouse to choose from the combo-box, I rarely do that when entering data. And as my store table is almost 1,400 records to start, maybe that's a good idea.

Thanks!

 

[Mr. B]

If you have that many rows being populated in your combo box, you may want to look at this link:
http://allenbrowne.com/ser-32.html

That's a lot of rows and I am sure you and your users are starting to type the value you need rather that just select a value from the list.