Combo Box issue

[mama16]

Hello,

Currently, I've created 5 tables. I want to create a form for data entry. I placed a few drop down boxes onto it along with some text fields. I want those boxes to pull the info from their corresponding tables. I can not get it to work.
For example, when someone is doing data entry, I want that person to select a value from a drop down box (Highway #). When the value is selected, I'd like for the Exit # drop down box to automatically pull the values, in this case "all the exit numbers" that are associated with the selected Highway #. That way the person who's doing the data entry will not have to look at all the Exit # that are in a table.

Any suggestions,

Thanks

 

[randy700]

How about something like.....

Private Sub cboHighways_AfterUpdate()
dim strSQL as String
strSQL = "SELECT Exits FROM tblExits WHERE Highway = " & me.cboHighways
me.cboExits.RowSource = strSQL
me.cboExits.Requery
End Sub

If cboHighways is a text value......
strSQL = "SELECT Exits FROM tblExits WHERE Highway = '" & me.cboHighways & "'"



Randy

 

[mama16]

Randy,

I'm getting an error "Method or data member not found
then Highlights me.cboExit.rowsource"

tblHighway contains one field, all the Highways.
tblExit has two fields Highway and Exit

On th e form I want the drop down box to pull the Highways from the tblHighway.
When a person chooses a Highway from the combe box I'd like the
cboExit to pull the exits that are associated with that highway

I went to the code builder session for the cboHighway and added the code. When I test the form, for example, select a Highway nothing happens. Then when I select the Exit in the cboexit, I get the runtime error

any suggestions?

 

[randy700]

If the only field in your tblHighways is identical to a field in your tblExits, your database is not normalized. That's the first thing I'd look into. Check threads here to get a better feeling on how to normalize the data.

If you're getting a runtime error when you select an exit from your cboExits combo box, you must have some code associated with the combo box.

What code do you have in the AfterUpdate event of the cboHighways combo box?


Randy

 

[mama16]

Thanks Randy,

This is the code I have

Private Sub cboHighway_AfterUpdate()

Dim strSQL As String
strSQL = "SELECT EXIT FROM tblExit WHERE HIGHWAY = '" & Me.cboHighway & "'"
Me.cboExit.RowSource = strSQL
Me.cboExit.Requery

End Sub

Also, the

tblHighway has one field

HIGHWAY PK

It's linked to tblExit by HIGHWAY

tblExit has three fields. These are;

EXIT_SEQ PK
HIGHWAY
EXIT

 

[randy700]

Make sure the Row Source Type for the combo box is set to Table/Query.


Randy

 

[mama16]

Randy, on the previous e-mail you mentioned that my tables were not normalized. I've been learning Access for about three months so I'm not an expert yet. Why aren't they normalized? I'm linking both table by Highway as a one to many relationship.

please share any comments

Thanks.

 

[willir]

Mama16

This is very similar to another post, but the variable names will differ...

http://www.tek-tips.com/viewthread.cfm?qid=980099&page=1

I assumed what you want to do is
- select a highway from the combo box cboHighway
- cboExit will display only the Exits for the selected highway

Your previous posts on your design include

http://www.tek-tips.com/viewthread.cfm?qid=972693&page=1

http://www.tek-tips.com/viewthread.cfm?qid=974263&page=1

Your design is quite reasonable. The minor tweaks I would consider are...
tblExit
HIGHWAY - text string
ExitNo - text string

Primary key is Highway + ExitNo. You don't want to add an exit twice to a highway such as...
[tt]
EXIT_SEQ Hyighway ExitNo
1 Hwy95 238A
2 Hwy95 238B
3 Hwy95 238A[/tt]

See how 238A can be entered twice.

Exit -> ExitNo
Exit is a common command / reserver word in the programming world, and using it as a variable name may cause problems later.

The other comment is that HIGHWAY as single field in a table is reasonable. However, if the name of the highway is changed, you may have a lot of data editing to do. Another design would to have treated the Highway as a descriptive name, and used a numeric number as the primary key...

HighwayID - numeric (autonumber?), primary key
Highway - text string

...but these are minor tweaks. I feel your design is fine.

...Moving on

Private Sub [COLOR=blue yellow]cboHighway[/color]_AfterUpdate()

I am assuming that you know how to name a combo box, and it is named per the highlight. Check the "Other" tab in the Windows property. Note that if you change the name AFTER entering an event procedure, Access will no longer run the code.

There is one other thing to be concerned about regarding naming controls and event procedures. With development, we may add and delete control, and we may use the same name. When you delete a combo box on the form that has event procedure code "behind" the combobox, the code is NOT deleted. If you add another combo box, and use the same name, will allow you to create another event procedure using the same name. Access does not complain until you run the code at which time it gets confused.

Scan your VBA code and ensure you do not have duplicate event procedure / subroutine names.

This leaves data type. You are already familiar with the use of quotes around text strings. Make sure the combo box data type matches the field type in the table.

...Moving on
Debugging

You are using the strSQL text string to store SELECT statement. This is good.

You have two tools available to you to help you along.

Use the "Immediate" window (Ctrl-G), and display the current value of strSQL by typing...

? strSQL

Evaluate the output.

The next tool is the query builder. Copy the strSQL output from the immediate window into your clipboard, start the query builder wizard BUT don't add a table. Switch to the SQL view and paste the clipboard contents into the SQL window. Make sure there is a ";" semicolin at the end of the SQL statement. Run the query. Access will point out any problems it has with the query. Correct the SQL statement until it is working. Make the required change to the event procedure VBA code.

Richard



You have

 

[mama16]

Richard,

Thanks for everything. I really appreciate it.
The reason I created the tblHighway is because the Highways are going to be static. Currently, I'm doing it for the state of Tennessee and I don't think those Highways are going to change. I'm hoping they'll never change!!! But I like your idea of having an autonumber in the table. I might consider that.
Also,

These should not happen at all on the tblExit table, having Hwy95 and ExitNo 238A twice
This table will link to the tblHighway by using the Highway field. It's a one to many relationship. Yes Highway number will repeat but not Highway number and Exit number together. It's not possible.

EXIT_SEQ Hyighway ExitNo
1 Hwy95 238A
2 Hwy95 238B
3 Hwy95 238A


The next table, tblMain

That's where most of the information is going to go to. The data will duplicate in here. For example,
There are only allow six customers per exits, so on I-95 at exit 238, I can have

Burger King
Texaco
Wendys
Lodging/Camping
Taco Bell
Arbys

so I-95 and exit 238 will appear 6 times. Am I making any sense?


I'm linking tblExit to tblMain by the Exit_seq. It's a one to many relationship. Exit_seq in tblExit is an autonumber.


tblMain

Autonumber PK
Exit_Seq, number
Service_Name, Text
Service_Type, Drop "Retaurant, Gas Station etc.."

Highway , Drop Down Box "All Hwy in a state
Exit, Drop Down Box All Exits for that Hwy
Priority, Drop Down Box Numbers 1 through 6
Phone, Number
From Date starting date
TO Date expiring date
Name OLE .wav
Details OLE .wav


Then, there are two more tables. These are shown below. Again the information for these two tables is also static. It won't repeat.


tblPriority (only numbers 1,2,3,4,5,6)

Priority PK

I'll be link to tblMain using the Priority field

tblService_Type (Gas, Food, Lodging/Camping,Road assistance)

Service_Type PK

I'll be link to tblMain using the Service_Type field

I thought of Making Highway and exit both unique but Access did not allowed it. I now in Oracle you can have a primary key and an unique key on the same table. I don't know how to do it here. Again, my experience with Access is very limited, I'll get better.

I just want to make sure before I build this DB that I'm on the right track and all the tables are link correctly

I appreciate the people who answered to my Post. You guys are great. Thanks and if you think I'm not on the right track please let me know so I can start all over again. I have to step out for a couple of hours. I'll see you guys later.

Thanks Richard

 

[GingerR]

Hi there. I have to say that based on your description, you COULD put in exit 238A multiple times in your tblExit because it's not part of a key. It's physically possible. In design view you can select both Highway and ExitNo and click the little KEY icon on the tool bar. That will ensure that the combination of Highway+ExitNo will only exist once.

What's Exit_SEQ for? Just an autonumber ID for Highway+ExitNo?

I don't understand the relationship between tblMain and tblExit, based on the Exit_SEQ number, because in tblMain you are also picking a Highway and an Exit. It's confusing. Seems like it would be duplicate info, but not quite. You'll have some Highway and Exit picked for a Exit_SEQ in tblExit, but then choose different Highway and Exit in tblMAIN for the same Exit_SEQ?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

As we work on an issue, we uncover yet more layers of detail. Very interesting project.

From the pure normalization way of thinking...
Regarding tblExits -- there is only one exit, so the best way to handle it is to use one record per exit.

You actually have a many-to-many relationship between exit locations and chain stores. Although you are using "exits" on a highway which is quite reasonable, exits are the same as locations. A chain store will have many locations, and a location may have many chain stores.

I have no problem with you using tblHwy. A tblHwy table can actually include other things such as jurisdiction, direction (N E S W NE NW ...), etc. For your purposes, you are most interested in the Hwy name. (By the way, I have seen the names of highway change, and I have seen a highway be called more than one name.)

Now, exit locations. You can have the exit number stored on each record for the specific stores -- this would work. Or you can establish a many-to-many joiner table between tblExit and tblStore, say tblStoreLocation. I would have to work with the data to see which is better the better design "down the road" ( ;-) ).

I whipped up a design and tested it as follows...

tblHighway
Hwy - primary key
Direction - text

Sample data[tt]
Hwy Direction
100 NESW
135 NS
266 NS
70i EW
I-24 NWSE
I-40 EW
I-75 NS
I-95 NS
[/tt]
tblExit
ExitID - primary key, autonumber
ExitNum - text
Hwy - text, foreign key to tblHighway

Sample data[tt]
ExitID ExitNo Hwy
1 230 I-95
2 231 I-95
3 232 I-95
4 350 I-75
5 352 I-75
6 354 I-75
7 356 I-75
8 072 70i
9 073 70i
10 85 70i
[/tt]
Note: I used ExitID because the table will be referenced in other tables -- easier to use on field than a multiple field foreign key. But I added an index for ExitNum + Hwy with no duplicates.

tblChainStore
ChainStoreCode - primary key
ChainStoreName - text
PrimaryPhone - text
Address - text

Sampe data[tt]
ChainNameCode ChainStoreName
Arbys Arbys
Arco Arco Gas
BKing Burger King
CInn Comfort Inn
Exxon Exxon
HowJn Howard Johnsons
MacDs MacDonalds
Shell Shell Oil
Tbell Taco Bell
Texco Texaco
Wendy Wendys
[/tt]
tblStoreType
ChainStoreCode - foreign key to tblChainStore
StoreType - text.

Sample data[tt]
StoreCode StoreType
Arbys FastFood
Arco Gas
Arco Service
BKing FastFood
CInn DiningFood
CInn Rest
Exxon Gas
Exxon Service
HowJn DiningFood
HowJn Rest
MacDs FastFood
Shell Gas
Shell Service
Shell CafFood
TBell FastFood
Texco Gas
Texco Service
Wendy FastFood
[/tt]
This differs from your design. I feel stores can have more than one type of service. Food + Inn, Gas + Service + Food, FastFood vs Restaurant, etc.

tblStoreLocation
ChainStoreCode - foreign key to tblChainStore
ExitID - foreign key to tblExit
CornerLocation - text
PhoneNumber - text
ManagerID - foreign key to Contact table (not shown)
PriorityCode - text or numeric

Primary key is ChainStoreCode + ExitID + CornerLocation OR ChainStoreCode + ExitID. It is reasonable that a store can only exist at one location or one exit.

Sample data[tt]
ChainStoreCode ExitID CornerLocation
Arbys 1 NW
Arbys 3 SW
BKing 4 NE
BKing 4 NW
HowJn 5 NE
MacDs 5 NE
MacDs 5 NW
Shell 5 NE
Shell 5 NW
TBell 9 N
TBell 9 S
Texco 4 NE
Texco 4 NW
[/tt]
The tblStoreLocation is similar to your tblMain where it defines the properties of the specific store.

Setting Start and End dates of service can be stored on tblStoreLocation as you have done, or on a separate table. If you store the Start / End dates on the tblStoreLocation or tblMain, you will loose your history from previous contracts. By storing the Start / End dates on a separate table, you can track history for previous contracts.

Now do you arrange your services by store (head office), by franchise owner (multiple stores) or by the specific store.

tblContract
ContractID - prmary key
ChainStoreCode - foreign key to tblChainStore
ExitID - foreign key to tblStoreLocation
StartDate - date field
EndDate - date field
ContractType - text, defines type of contract

Having said all this, I am sure your design will work. As I see it, the minor advantage of this design is that only one exit will exist, and a store can only exist at once at a location.

I thought of Making Highway and exit both unique but Access did not allowed it[/b]

With the table open in design mode, open the Index window, from the menu, "View" -> "Indexes"

Setting Indexes is fairly straight forward -- Add a name, select a field and specify index properties -- sort order, primary, unique, ignore nulls.

To set a multiple key index, add the name, select the first field, then under the first selected field, select the second field. Use the line with the index name to assign properties for the multiple field index, such as primary for ChainStoreCode + ExitID + CornerLocation, or Unique for ExitNum + Hwy.

...Moving on
Your question is about designing the form.

Aside from your maintenance forms, you probably want to approach the data in two ways...

First by specific store (tblStoreLocation or tblMain) where you enter the service type, etc, and review the information when required.

Your use of combo boxes to link the foreign key to the related table is perfect. If you decide to accept my notion that there is a 1:M for the StoreType, then you will have to use a contineous form for StoreType, or use a ListBox and use the multi-select option.

Although the contract information should always be available, you may want to protect it so as to not have it changed inadvertantly.

Helpful hints...
- Add command buttons or use the double-click event procedure to allow the end user to open a support form. When you start entering data, you may find that you need to add new Exit location records, so it allowing the end user to add these on-the-fly would be useful.
- After adding a new entry, you can either use the NotInList event procedure or Me.YourComboBox.Requery to ensure the new record is immediately accessable to the user.
- Protect your data. A common approach is to open the form in "edit" mode whereas perhaps the most common need is to review the entered data. Protect the data by having the user click a command button or toggle button to switch from no-edits to edit mode.
- Add unbound combo boxes on the form or in the header section to fascilatate the retrieval of records quickly.
- You may have to spend time on your Contract of service. I can see having the details on the contract on-line would be really helpful.

Well there is lot to think about here. Hopefully, I have helped and not confused.

Richard

 

[mama16]

Thanks a lot willir (MIS) for your comment. I liked your design, you actually included more information than what I need. I see that on the tblExit you indexed both Hwy and ExitNum with no duplicates allowed. That can not be possible because Highways and ExitNum are going to duplicate. For Example,

Highway ExitNum

I-95 SOUTH
I-95 NORTH
I-264 3
I-264 101A
I-264 5
I-295 10
I-295 3

In the above example, Both Hwy and Exits repeat more than once.

If I give both Highway and ExitNum an index with no duplicates, then Access will give me an error and I will not get the results I want.
Remember, I will have six customers per exit so my information is going to look like

Chain_Store_Name EXIT_NUM

Burger King 15A
Taco Bell 15A
Mc. Donald's 15A
Exxon 15A
Days Inn Hotel 15A
Texaco 15A
Texaco 102
Arbys 102
Mobil 102
Burger King 102
Chevron 102
Taco Bell 102


What do you mean by this "As I see it, the minor advantage of this design is that only one exit will exist, and a store can only exist at once at a location." Are you referring to your design or my design?

Also, in your diagram I see that you're linking tblContract and tblStore_Location by Exit_ID. When you do this, you get what it's call an "Indeterminate" because none of them is unique.

Anyway, thanks for replying to my post. I truly appreciated.
Please let me know if I'm wrong.

Monica

 

[willir]

Hi again Monica

First, I think your design is well on its way to "production".

ExitNum + Hwy with no duplicates

You want to avoid entering duplicate entries. For example, if you enter I-95 + exit 101, and then enter it a second time or even a third time, there is nothing in your system to differentiate each of the entries. This is part of the normalization process. Please understand that the index for no duplicates is for the highway number + the exit number. You could extend the index for duplicates to highway number + the exit number + corner / direction if requried. This type of requirement would be recommended if you use a M:M joiner table.

If you want to use the more "flat file" approach, then Hwy and Exit number mearly become one more properties for the record.

Perhaps I am over-complicating things. You seem to be looking at each record for a store having a highway and an exit. I am looking at it from the other way -- an exit on a highway, as a location, will have X number of stores.

They seem to accomplish the same thing, but the different approaches will affect how you engineer your combo boxes.

As I see it, the minor advantage of this design is that only one exit will exist, and a store can only exist at once at a location

This refers to my design where you can only have one location. A problem I have seen all-too-many times is when a design allows the user to enter a field that will be queried later on. For example, a mailing address database allows the data entry person to enter the Street name -- sounds reasonable. But the postal service offers reduced postage rates if you present the items to be mailed pre-sorted by street and city. Now, there are several entiries for Grey Street. The operators enter...
123 Gr[COLOR=blue yellow]a[/color]y St.
345 Grey St[COLOR=blue yellow] [/color]
245 Grey St[COLOR=blue yellow]reet[/color]
19A Grey St[COLOR=blue yellow]r[/color]
124 Grey St.
...Because "Grey Street" is spelt differently for each record, Access has no way of sorting the mail by the street name. It would have been far better for the operator to "pick" the street name from a list (combo box) to ensure consistancy instead of having the operator(s) spelling it for each entry. And one way to offer the "Street" name is to have a table that stores the name of the streets for each city. (This is a real life example)

Hopefully, the example explains the difference.

Since I suspect you will be entering the data yourself, I am sure you will be consistant with the data entry.

Indeterminate

This is just another way of saying that records for Hwy + Exit are not unique (with your design) so if you enter several duplicate records, you have no way of knowing which of the duplicates your query retrieves.

If you search the Access forums for duplicate records, you may see various Tek-Tip users complaining about duplicate / trying to get rid of duplicate records.

...Moving on
Your original post was regarding how to get the combo boxes to work. Depending on the design, you use the first combo box to restrict the selections for the second combo box.

cmbHwy - Combo box for highways.
RowSource - SELECT * From tblHwy Order By Hwy

cmbExit - Combo box for exits
RowSource - SELECT * From tblExit Order By Exit
changes to
Rowsource - SELECT * From tblExit WHERE Hwy = "Me.cmbHwy" Order by Exit

This is a simplistic view. The actual coding would be closer to...

AfterUpdate event procedure for cmbHwy

Dim strSQL as String, strQ as String, strID as String

If Len(Nz(Me.cmbHwy, "")) Then
   strID = Me.cmbHwy
   strQ = Chr$(34)
   strSQL = "SELECT ExitNum From  tblExit WHERE Hwy = " _
   & strQ & Me.cmbHwy & strQ
   Me.cmbExit.RowSource = strSQL
   Me.cmbExit.Requery
End If

Please correct the above for any differences in spelling for your database. And yes, this is very similar to the original post by Randy

Note that the ExitNum for the SELECT statement is the only record returned. This SELECT statement will change per your design, and the current statement has no way of differentiating Exit number after it has been selected. It almost always better to select the primary key from the source table to be used as the foreign key in the data entry table.

Richard

 

[mama16]

Thanks again Richard.

This might be something simple but I'm not getting it to work.
I changed my design a little bit. I'm actually making HWY+EXIT to be unique because even though I will be entering the data, I don't want duplicates.

In design view, I'm selecting both HWY+EXIT and then hit the key button. Access is making them to be unique.
Then, I go to the field properties window and for each of them I select "Index(No duplicates)"
When I enter the data shown four lines down, I get an error (It would create duplicate values in the index, primary key, or relationship"

HWY EXIT
I-95 3
I-95 4

I tried it by not giving them a primary key but assigning them "both of them together" an index with no duplicates Access still gave me an error. What am I doing wrong?

"""""""""This is from willir (MIS)

Sample data
ExitID ExitNo Hwy
1 230 I-95
2 231 I-95
3 232 I-95
4 350 I-75
5 352 I-75
6 354 I-75
7 356 I-75
8 072 70i
9 073 70i
10 85 70i

Note: I used ExitID because the table will be referenced in other tables -- easier to use on field than a multiple field foreign key. But I added an index for ExitNum + Hwy with no duplicates."""""""""

Like I mentioned when I do this, Access will not allow me to enter another record.
I'm not entering I-95, Exit 3, twice. That's exactly what I want to avoid.

Thanks,

Monica

 

[PHV]

Monica, feel free to consult the help engine for creation of unique composite index.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244