Using an Option Group to change controlsource of text box

[Danielle17]

Hi - (Using Access 2K)

On one of my forms I am using an option group to change the recordsource of my combo box. The option group has 3 options to chose from "First", "Second" or "Third" each of these representing a different table in the database. Each one is very similiar in design but none are identical.The combo box displays the names and addresses of customers from one of the three tables. Now after the user chooses a table to pick a customer from they will then precede to choose a customer out of the combo box. The info for this customer should be displayed in the text boxes on the form. The only problem is that I cannot directly assign a control source to the text boxes since it will change according to which option is choosen(which table). So logically I thought to put a line of code with the option group which states that the recordsource of the FORM will change to the which ever table is choosen. This is what I put down for the controlsource for the textboxes:
Me.txtPhoneNumber.ControlSource = "PhoneNumber"
This produces an error that goes something like "A macro or function prevents this from happening because the data cannot be saved". The controlsource for the text boxes is going to change, like the reocrdsource for the combo boxes, when one of the options in the group are choosen. I'm at a loss as to where I should be putting the bit of code that states the controlsources for the textboxes. I cannot put it on the OnClick event for the option group because of the error....I need some help fast on this one...it needs to be on the sales manager's desk ASAP....thanx in advance...I can post the code that I'm currently working with.

 

[Maquis]

Well, here's an idea. There probably is a more efficient way of doing this, so if someone else has a better idea, speak up.

I would put 3 sets of text boxes on the form. 1 set per table. After the user picks a table, turn the visible property of 2 sets to false and only display the set chosen. You can put the text boxes right on top of each other so the user doesn't notice any re-positioning for the different tables.

 

[braindead2]

First I question why are customer names stored in 3 different tables?.

Then If I understand what you are trying to do. The control source is determined by the form not the textbox. I Suggest you make the form unbound and fill the data using DAO or ADO or if unfamiliar with VBA you could have the Combobox that selects the customer on an unbound form and the information you want to fill on 1 of 3 subforms. Then based on the selection of the option group will determine which subfrom you make visible.
good luck

 

[DougP]

if your fields are the same IE
Phone is spelled exaclty the same way in all 3 tables
then you can do this

Unbind the form, so "recordsource" is blank

Make sure the phone "control source" is Phone

use this VBA code to set the form to which ever table you like so:
----------------------------
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

Dim Tablename As String
' set the table name to which ever table here
Select Case Frame10.Value
Case 1
Tablename = "Table1"
Case 2
Tablename = "Table2"
Case 3
Tablename = "Table3"
End Select
Me.Form.RecordSource = "Select * From [" & Tablename & "];"
'Me.Requery

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub



Ok have fun
;-)
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[DougP]

Oh I forgot one thing

put this in the unbound form Recordsource

SELECT * FROM Table1 WHERE False;

Where table1 is on of your valid tablenames
Or you will have all of your fields #Name? DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Danielle17]

There are 3 different tables because each has different things that they do in a different part of the database...The tables cannot be combined....

I like the over-lapping text box idea...it kinda sounds a little "messy" but I think I can make it work....

Thanks for both of the suggestions

 

[Danielle17]

Not all of the names are identical...I did not set this database up, otherwise they would be If I change them I'm not sure what it will affect in the rest of the database....