Default value with manual override in case of NULL (1 Viewer)

[mheijligers]

[SOLVED] Default value with manual override in case of NULL

Dear all,

I've searched through the forum to find a solution for my problem, but I didn't find an answer yet. So that is why I created a new thread.

I have a single form with several drop combo boxes. My question is about 4 of the combo boxes: Product, Country, Customer and Main customer. Functionality I'm looking for is, when I select a product from the combo box 'Product description', the other 3 combo boxes are filled automatically with the default value from the database table. Data for those 4 combo boxes is stored in 1 table.

Unfortunately, there are a lot of null values in the database table (a product can be available in more than 1 country), so manual override (via row source = "select country from table where id = 1") should be possible. For every combo box there is always 1 value needed. I've tried to implement a dlookup, but then manual override in case of null was not possible. Anybody has an idea how to solve this?

Thanks in advance.

Marc

 

[Uncle Gizmo]

Will need to know more details about your table structure Marc

 

[Uncle Gizmo]

Some Combo Box Videos that may provide inspiration, or possibly perspiration!

Cascading Combo Box 1

MS Access Get Info From a Combo-box 1

Create a Form with Basic Combo box Lookup

 

[mheijligers]

Tony, thank you for your reply and for providing the links to the videos. I was able to solve the issue.

In the vba of the form I've added the following code:

Private Sub Form_Current()
Me.ComboCountry = Me.ComboProduct.Column(3)
Me.ComboCustomer = Me.ComboProduct.Column(4)
Me.ComboMainCustomer = Me.ComboProduct.Column(5)
End Sub

In the after update event of the ComboProduct dropdown, I've added the following code:

Private Sub ComboProduct_AfterUpdate()
Me.Requery
End Sub

In this way, the fields for Country, Customer and Main Customer are filled automatically after product selection. In case there is no value for Country, Customer or Main Customer in the table, manual selection of the drop down list (select statement) is still possible.

Regards,

Marc

 

[Uncle Gizmo]

Hi Marc, If you found my YouTube Videos helpful, then subscribing to my YouTube channel would be a great way to thank me....

Sent from my SM-G925F using Tapatalk

 

[mheijligers]

hi Tony, I've just subscribed to your Youtube channel. Your MS Access (combo box) videos are really helpful!

Thanks again!

Regards, Marc