COMBO BOX HELP Required

[bluenose76]

I have a form that i would like to display a specific record based upon what is selected in a combo box.

If is generate the form using hte standard wizard then all the relevant fields appear as you would expect, however when i select and change the contents of my combo box, nothing else changes with it.

If i create a new combo box on the same form a numeric field is displayed however when i select and use this combo box the records do change as i would like them to.

I have looked at the code for the after update procedure and i cannot for the life of me getr it to work on the way in which i want, can anyone help me please?

 

[RuralGuy]

Post both AfterUpdate events and we'll help sort it out with you.

 

[bluenose76]

There is no event procedure assigned when i create the form using hte standard wizard, however the form displays the contents correctly.

when i create a combo box myself and tell it to find records based upon the criteria i select the following event procedure is added:

Private Sub Combo16_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[VRN] = " & Str(Nz(Me![Combo16], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

the above event procedure does not however display the data the way in which i would like but it does find the records based upon the selection.

I have tried copying this event procedure and mirroring it by changing the name of the combo box and what is inside the [box brackets] but no joy so far.

It is reallyt frustrating to say the least

 

[RuralGuy]

Because of the way you worded your response, I suspect you are using an Evil Lookup Field in your table. Is that true?

 

[bluenose76]

yes theh table is originally fueled by a lookup field for the VRN, however i have created a query on the table and form from the results of the query?

I must admit, i was earlier tryign to filter records from a query based upon a lookup field and it would not allow me to do it which is frustrating because the only common denominator is the VRN field.

 

[bluenose76]

Rural Guy,

I have just read the page which you linked to informing me of the "Evil Lookup Field".

This now poses me with a different question all together, and one which you may noow be expecting.

When I was learning the basics of database design, I was taught that having multiple tables with data that is referenced in many places is the best way to go otherwise I would end up with a flat file similar to that of an excel spreadsheet? Having now been educated in the pitfalls of using the Lookup field I now realise that this may not be the best way forward, However, what is the best way forward i now do not know?

What would your reccomendation now be?

should I have a table that stores all of my VRN's and the other tables reference it? if so then how do I acheive this without using a lookup field?

Do I manually enter the VRN each time in all table sthat requires it?

Or is there another way of acheiving this that i am unaware of?

Thank you in advance for your time and help.

 

[RuralGuy]

Completing a field with a ComboBox is just fine. The problem is that this should be done on a form and *not* in the table. Go into the table in design mode and change the ComboBox lookup to a textbox. You will then be able to see what is in that field in the table when you use it in a form. Do your "looking up" on a form with a ComboBox. Please post back if you wish more details and further assistance.

 

[bluenose76]

RG,
I am at a loss I am afraid, I have done what you have said and changed the lookup field to a text field,

This being done i am still at the point where by my forms combo box will either show the correct data but will not change record when i select a different VRN or it will show a numeric value but will change the records?

am i being a complete biff here?? if so then please tell me so

 

[RuralGuy]

Any chance you could post your db? Or at least a stripped down version that demonstrates your problem?