Changing the destination table based on a combobox selection

[JCColindres]

Hi guys,


I'm stuck trying to solve this:banghead:, I have a form to entry data for daily production this data goes to a table called Cases, the table cases doesn't allow duplicate case numbers so far so good.


Now I have a combobox with values A B and C and I need to be able to save the data from this form on a table named Collabs instead of the Cases table when value C is selected on the combobox.


Is this even possible? hehehe I haven't been able to find any reference to this online.

 

[vba_php]

I'm going to step outside my comfort zone and talk like the pros here....what you want to do is not normal, and indicates that you might not be setup properly in terms of your architecture (oh my, another "being proper" statement from me! ). what you need to do requires coding because a form can only be bound to one table (or a query, which can pull from multiple tables). but to give more details about answer that will be suitable for you, you have to provide details about things...for instance, is there any relationship between tables "collabs" and "cases"? what's the current recordsource of the form you're talking about?

 

[JCColindres]

Hi Adam

I figured this is an odd scenario, I apologize this is my 1st DB but to answer your questions

My form is saving datat to the cases table

The table cases has all of our production and we have 3 guys assigning cases cases numbers are unique to avoid a report showing the same case assigned to 2 agents on the same day since I use the case number and timestamps to get production reports.

Now the collabs Table will be used in the situation of an out of the office agent is and the case will be assigned to a new agent for a day or 2.

I need this on a different table so it does not affect my main production report, I figured that I from this other table I can pull a query that later I can add to the totals of my reports.

 

[June7]

If the two tables have identical structre - same fields - could just have another field in one table that identifies records as "Collab" and then apply filter to exclude those records from reporting. Could be just a yes/no field.

 

[vba_php]

Now the collabs Table will be used in the situation of an out of the office agent is and the case will be assigned to a new agent for a day or 2.

June is right in some sense, but he/she (are you male or female June?) but might fall short in one aspect. If you need a history of records regarding "who assigned what", then what you probably want to do is create one record entry every time a case is assigned by an agent, regardless if the agent is a substitute one or not). You haven't clarified whether or not you need to keep track of that type of info, but if you do then simply adding a checkbox that indicates "collab" or "cases" as it pertains to "who assigned what" would not work because it wouldn't provide the historical record data.

 

[Uncle Gizmo]

Re:- Now I have a combobox with values A B and C

I agree with June, add a new field. I would just add that you could use the A, or B, or C from the combobox in the new field to identify the different records.

In effect, the extra field would provide you with 3 tables in one.

Three sets of independent data, as identifed by the A, B or C....

Sent from Newbury UK

 

[JCColindres]

Thank you guys, I dint think of that, ill give it a try and let you know on Monday

 

[June7]

And yes, if you need some sort of history, there would be 2 related tables - parent and dependent - and they would not have identical structure.