Country Code Calculation

[nchesebro]

I am working on the address portion of my database now.

I have a listing of all USA States, Canadian Provinces/Territories, and Mexican State Abbreviations from http://www.iowa.gov/tax/forms/84055.pdf

When a user is completing this particular form, they will enter the city, state, and zip. (I know I can get zip codes too, but our organization lacks the funds and resources to maintain an up to date listing at all times for that option to be practical) When they select the state/province abbreviation from a lookup box, I would like a calculated field to associate the appropriate three digit country code (aka, USA, CAN, or MEX).

I am not real experienced in calculated fields, any pointers?

[alansidman]

I would probably do this with a Case Select Statement in VBA

Here is information on that

http://www.databasedev.co.uk/select-case-statement.html

Alan

[nchesebro]

Would the information in that source also apply to this situation:

When a student calls in to make a tour reservation, they are asked what programs they are interested in. They may ask for some program that we do not have or an incorrect program-degree type. Therefore, in the reservation form, the inputter will select either Master, Bachelor, or Associate, and the selection there would trigger a query to find all programs in that degree class and populate another drop down menu with the appropriate programs per the selected degree.

[alansidman]

have you reviewed the site

[nchesebro]

I have. It will take some time for me to figure out how to make that source material apply to the first situation, but I think it would be inappropriate to try to use the same thing for the second scenario. For the second scenario, I think the use of queries would be more suitable...

[alansidman]

The second scenario is called cascading combo boxes. Here is a tutorial on them.

http://www.fontstuff.com/access/acctut10.htm

You may also want to look at this site and view the tutorials on combo boxes.

http://www.datapigtechnologies.com/AccessMain.htm

Alan

[nchesebro]

Okay, so I was sucessfull at creating the cascading combo boxes, once. The second instance I was not. If you open my database, go on to the frmReservation, and scroll to the bottom half where you will see an area titled Ticket Requests. You can select a ticket type (ie Meal or Athletic) but the second combo box does not populate with anything. Maybe it was a simple simple mistake in my vb code behind and I continue to overlook it, or maybe it has something to do with the way the form itself is structured? What I do know for sure though is that I have exhausted every technique I know to resolve the issue and it isnt working .

Also, on a side note, at the top of the form, you can select a State/Province. I have tables and relationships set up to determine what state/province belongs to what country and have a query set up that should update the Nation field to the appropriate country code after the state is selected, and that too will not work, despite the same type of thing working for the Date/Day calculation... Stumped again.

I appreciate your help

[nchesebro]

...and here is the database (whoops)

[boblarson]

Did you make sure to change the Combo's COLUMN COUNT property to be the right number of columns and refer to the column right (zero-based)?

I can't download and look at your db right now, so I just thought I would throw those ideas out there for you.

[nchesebro]

It was initially set to 1, changed it to 0, no luck there...

[boblarson]

It was initially set to 1, changed it to 0, no luck there...

Column Count is not zero based. So if you have 2 columns it needs to be set at 2. It was the reference of

=[ComboNameHere].[Column](1)

that I was referring to as far as zero-based numbering (where the example would be the second column).

[nchesebro]

I have the Combo based on either one of two single column queries, which is determined based on the selection of the first combo box.

"=[ComboNameHere].[Column](1)"

Where would i find that expression at? I cant find one that resembles that other than that in the row source of the determining combo box...

[boblarson]

I just thought of something. Did you make sure to Requery the second combo box in the After Update event of the first?

(and you can ignore the other stuff I said, as it appears not to be applicable)

[nchesebro]

Maybe it is in the wrong spot in the code? Just checked that and in fact that was absent. Still not working though... maybe I have it in the wrong place?

Private Sub cboTicket_AfterUpdate()
Me.cboType.Requery
On Error Resume Next
Select Case cboTicket.Value
Case "Athletic Tickets"
cboType.RowSource = "qryAthleticTickets"
Case "Meal Tickets"
cboType.RowSource = "qryMealTickets"
End Select
End Sub

[boblarson]

Yep, wrong place. It should be after.

Private Sub cboTicket_AfterUpdate()

Select Case cboTicket.Value
   Case "Athletic Tickets"
      cboType.RowSource = "qryAthleticTickets"
   Case "Meal Tickets"
      cboType.RowSource = "qryMealTickets"
End Select
   
   Me.cboType.Requery

End Sub

And I would get rid of the ON ERROR RESUME NEXT because that can obscure some problems that might be underlying things.