Query Problem!

[tstowe]

I have TblUnit that stores Unit data with the main field being the UnitUIC (unit identification code). The UnitUIC connects everything from all of the tables as to who and what goes where. The are two UIC's: WPB7AA (the HQs) and WPB7A1 (1st Detachment).

I have TblParaLin (Paragraph and Line) that stores the pertinent job descriptions that are separated to each location by the UnitUIC field (which is the Relationship mainstay). This table also stores the relative job identifier number (i.e. 92A20 for Supply) and job description.

I have TblSoldier that stores the personnel data that is also tied into the unit through the UnitUIC field. This form is also linked to TblParalin through field PMOS (Primary Job Skill - to keep it simple).

*** The Form ***

On the form for TblSoldier I have a field for UnitUIC for the user to determine which of the two units the soldier belongs. I also have a field that asks for the PMOS of the soldier. The PMOS field is pulling data from TblParalin in a combo box.

*** The Issue ***

When the user selects the appropriate UnitUIC I want PMOS to list only the jobs that pertain to that unit based on the data in TblParalin on the UnitUIC field.

*** What I have done to solve ***

I have gone into the combo box and set the Control Source for PMOS. I have the following statement in the Row Source property: Select [PMOS] from TblParalin where [UNITUIC]="WPB7A1". This immediately causes an error message of "Data type Mismatch" - which I believe is referencing something I have done wrong in the tables, but I haven't been able to track the source.

Looking at what I have done. . .this doesn't answer the issue of the user selecting the second UnitUIC, WPB7AA. Based on above, and I figure this out, the field is still going to show only the jobs available at the first unit.

HELP!

Thanks ahead of time.

Tony

 

[LonnieJohnson]

Hello tstowe,

I will take a stab at this.

1. You need to go to the design view for TblParalin and see what the "Data Type" for UNITUIC is. Based on your criteria "WPB7A1" it should be Text.

2. Secondly, you probably shouldn't hardcode your criteria since you have more than one UNITUIC, which you've discovered you need to be able to see the PMOS's for WPB7AA. What I would suggest is to put a list box on your form with the two UNITUIC's and allow the user to pick which one they want. Then your combo box's RowSource would reference the list box as such...

Select [PMOS] from TblParalin where [UNITUIC]= [Forms]![MyFormName]![MyListBoxName]

You will also need to add VBA to the list box's after update event to requery the combo box.

Hope this helps.


ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[tstowe]

Thanks Lonnie,

On your last statement concerning the VBA code in the afterupdate property to requery - do I simply put:

me!form.requery

or is it that simply??

 

[LonnieJohnson]

Me!ComboBoxName.Requery

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[tstowe]

I was slightly wrong, Thanks.