How to change the control source of fields by selecting a value in combo box?

[chaitu19]

Hi,

I have a Form with record source set to a Table1 and all the fields' control source in the Form are set to the fields in the Table1. And by clicking "add" button, the entered data in the Form gets saved as a record in Table1. Now what I'm searching for is, when I click a value in a combo box in the Form, all the fields' entered data should get saved in Table2 on clicking the "add" button.
I'm a novice in coding. I appreciate any help.

[Seth Schrock]

So you want the same data entered in table2 as you are saving in table1? This seems like a very poor design, as you should almost (like only 0.0000001% of the time) never had duplicated data in a relational database (such as Access). What are you trying to do? We may be able to help you come up with a better solution.

[chaitu19]

No, We don't want the data to be saved in Table1 when we click a unique value in combo box. The data should only get saved in Table2.

[Seth Schrock]

So in other words, you want the record source of the form to change to table2 if you select the appropriate value in the combo box?

[chaitu19]

Actually I did that, by selecting the appropriate value in the combo box, the record source of the form changes to Table2. But the data entered in the fields are not getting saved into the Table2, when I click the "add" button.

[jforbes]

I'm curious to see how you are doing this. Are you using Bound Controls, bound to Table1, or are you using Unbound Controls and then creating the SQL Insert command and executing it when the user clicks the Add Button?

For this scenario it would probably be easiest to Execute the SQL OnClick.

[Seth Schrock]

We would need to see the code behind the Add button. Please use code tags (the [CODE/] button) when posting.

[chaitu19]

#jforbes, I'm using Bound Controls, bound to Table1 when the user clicks "add" button.

#SethSchrock, this is the code behind "add" button on click()

DoCmd.RunCommand acCmdRecordsGoToNew

[zmbd]

If the fields in Table2 have the same name as in Table1, then by setting the [record source] for the form to the correct table should be the ticket. Otherwise, it sounds as if you have hardcoded the [control source] for each control to the first table.

As stated, we'll need to see your code.
Please follow the guidelines found in the following link before posting: [*] > Before Posting (VBA or SQL) Code

[Seth Schrock]

I'm having trouble getting around the idea of why the two tables still. I'm thinking that it would be a lot easier if you added a Category field to Table1 to separate the records instead of tables. You can create queries that act as virtual tables to split the data out. You should look at Database Normalization and Table Structures.

What is the code that you are using to change the record source of the form?

[zmbd]

Seth,
I'm thinking OP is on the mindset of late-bound tabbed control type concept. Using one form for multiple tables.

Still, as you said, more than likely a better way to do this if OP will explain the idea behind the project.

[chaitu19]

Table2 does not contain all the exact fields in Table1. It's like, when we select a specific value in combobx, some of the fields in Form gets disabled and remaining Fields' data gets saved in Table1 and until now we entered saved the data in Table1, but now, when we change the specific value in combobox to save in Table2, we are encountering this problem.

This is the code I used and it's not allowing me to enter the data saying "Control can't be edited, it's bound to unknown field "field name""

Private Sub cbo_AfterUpdate()
Dim strOption As String
strOption = Me.cbo
Select Case strOption
Case "Community Development":
Form1.RecordSource = table2
End Select
End Sub
 

And this is the code I'm using for "add" button

Private Sub add_click()
DoCmd.RunCommand acCmdRecordsGoToNew
End Sub
 

[Seth Schrock]

You would need to enclose your recordsource in double quotes.

Me.RecordSource = "table2"

[chaitu19]

It works perfect.
Thanks to all of you.

[Seth Schrock]

Glad we could help. Good luck on your project.