Updating a Field in a Table Based on a Combo Box Selection in a Form

[MJ_Wilkinson]

Hi all,

I have a combo box on my form that shows only certain Customer IDs from a Customer ID table that I want the user to be able to select (this table has two fields [Customer ID] and [Available]. I've done this by including that table in the Row Source as a query with [Available] set to "True".

I want to now complete this so that once a user has selected an available Customer ID this then updates the [Available] field in the Customer ID table. How can I make this happen?

Sorry this is probably really simple for someone and I think this will need VBA in the 'After Update' property. If somebody could give me some guidance on that it would be greatly appreciated.

 

[isladogs]

I'm confused...
The combo has a row source query where Available=True
The user selects a CustomerID from that field which then updates the Available field .... to False?
Which means that customer ID disappears from the combo box.
Eventually the combo will have no records????

I'm sure that can't be what you meant but as I'm reading it, it is what you've described!

 

[MJ_Wilkinson]

Hi Ridders,

So yes I do want to update the Available field to False. This isn't a problem as I've an extremely large number of Customer IDs (which I'm never going to reach).

However I think you're saying it would disappear immediately after update, which is a contradiction so actually not probably right.

I think maybe if I explain the purpose of the form that might help. The form is a customer entry form to enter customer information, and I'm using this combo box with row source query to ensure the user can only select Customer IDs that haven't yet been taken. Say in this example they select "Cust1". Once they have saved and exited the form, the next time the users go in to add a customer I don't want them to select the same Customer ID, so I don't want "Cust1" to show up as an option in the combo box.

I hope that makes some sense.

 

[MarkK]

...this table has two fields [Customer ID] and [Available].

This doesn't make sense to do. If the availability is correctly a property of a customer, then it should be a field in the Customer table. When you link a value to a row in a parent table, you do so because there is a one-to-many relationship between the object represented in the parent row, and the many child objects in the related table, but this doesn't make sense in the structure you've described. What would it mean for the one CustomerID to have many rows in this availability table? And if it is not the intent to (at least possibly) have many rows in the child table, then a related table is not the solution to the problem.
hth
Mark

 

[isladogs]

No I wasn't saying the name would disappear immediately for that user.
However it will no longer be visible when the combo is next opened by the same user or anyone else. And unless there is some other method of reversing the process, no way of getting the name to reappear at any future time.

So to me, no it doesn't make sense. It would however be easy enough to do.

 

[Mark_]

Hi all,

I have a combo box on my form that shows only certain Customer IDs from a Customer ID table that I want the user to be able to select (this table has two fields [Customer ID] and [Available]. I've done this by including that table in the Row Source as a query with [Available] set to "True".

I want to now complete this so that once a user has selected an available Customer ID this then updates the [Available] field in the Customer ID table. How can I make this happen?

Sorry this is probably really simple for someone and I think this will need VBA in the 'After Update' property. If somebody could give me some guidance on that it would be greatly appreciated.

Let me see if I understand what you are TRYING to do...
1) You created a table with a massive number of "Customer numbers" that you want your users to be able to select from when adding a new customer.
2) These customer numbers are not just a numeric sequence.
3) You want to make sure user "A" doesn't select the same customer number as user "B", and you want that done at the moment user "A" selects it from the drop down.

This type of system is not very robust, is prone to errors (User "B" opens the form first so their combo gets loaded, User "A" then opens their form and promptly selects the first available customer ID, the user "B", who's somewhat tardy in doing the same goes and selects the SAME first available customer ID as the query loads when they come into the form) and really offer no advantage over having an autonumber field for customer ID.

If you want to have something "Custom" to make the customers feel good, that would be a user entered field that checks against your database to see if it already exists, much like selecting a Username here.

If you are actually looking to do something else, please let us know. Also write out the business flow for what you are trying, possibly including the file structures you are working to update.

 

[isladogs]

Final reply from me.

You now have three of us all saying this is a bad idea.
All of us could easily tell you how to do this...it isn't difficult.

Notice that none of us have done so...
...in my case because I don't want to help you in a bad decision.

I expect someone will give you the solution...but do think hard before you implement it.

Good luck with your project