Dynamically change record source/control source on a form

[beacon]

Hi everybody,

Using Access 2003.

I'm trying to dynamically set the record source for the current form and set the control source for a text box to input data to one of three tables.

I have a form (frmAddNewUser) that will add users to one of three separate tables (tblDefsAFP, tblDefsMSU, tblDefsWF) based on the selection of an option button from an option group on the same form.

I have a textbox (txtNewUser) that accepts the name of the user and will have it's control source set by one of the three option buttons below it.

So, to recap, the user name is entered in a text box, one of the option buttons should be selected, and based on the selection of the option button, the record source for the form should be determined and the control source for the text box should be determined.

Oh, and everything launches based on a submit button.

Right now it accepts everything as if it's working, but the name entered into the text box isn't appearing in the table/query specified.

Here's the code:

Private Sub cmdSubmitAddNewUser_Click()
 
    Dim subVal As Variant
    Dim savVal As Variant
    Dim UserChoice As Variant
 
    If IsNull(Me.NewUser) Then
        MsgBox "You must enter a user name", vbOKOnly + vbExclamation, "Empty User Name"
        Exit Sub
    End If
 
    UserChoice = [optionGroup].Value
 
    If IsNull(UserChoice) Then
        MsgBox "You must select a campus", vbOKOnly + vbExclamation, "Campus Error"
        Exit Sub
    End If
 
    If UserChoice = 1 Then
        Me.RecordSource = "qryAuditorAFP"
        'for debugging purposes
        MsgBox Me.RecordSource & " me.name " & Me.Name
        Me.txtNewUser.ControlSource = "Auditor"
        MsgBox Me.NewUser.ControlSource
    ElseIf UserChoice = 2 Then
        Me.RecordSource = "qryAuditorMSU"
        Me.txtNewUser.ControlSource = "Auditor"
    ElseIf UserChoice = 3 Then
        Me.RecordSource = "qryAuditorWF"
        Me.txtNewUser.ControlSource = "Auditor"
    End If
 
    subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion, "Add User")
    If subVal = vbYes Then
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close acForm, Me.Name
        MsgBox "User was entered successfully", vbOKOnly + vbInformation, "User Added"
        savVal = MsgBox("Would you like to enter another user?", vbYesNo + vbQuestion, "Add User")
        If savVal = vbYes Then
            DoCmd.OpenForm "frmAddNewUser", acNormal
        ElseIf savVal = vbNo Then
            DoCmd.OpenForm "Switchboard", acNormal
        End If
    ElseIf subVal = vbNo Then
        MsgBox "User was not added", vbOKOnly + vbInformation, "User Not Added"
        DoCmd.CancelEvent
    End If
 
End Sub
 

Thanks, in advance, for the help

[jayjayplane]

I am trying to give you some clue, hopefully it is at the point...

This part of my project's VBA code:

Dim stLinkCriteria As String
...............
stLinkCriteria = "[A_NUMBER]=" & "'" & Me.txtSSN & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
.............

I have a text field for use to data entry, and use stLinkCriteria to make sure table/subform also automatically recorded a_number that user just data entried...

[beacon]

Hi JJP,

I'm not sure that this helps me out. Were you trying to post your code in a different thread?

Anyone else have any ideas?

[jayjayplane]

Hi JJP,

I'm not sure that this helps me out. Were you trying to post your code in a different thread?

Anyone else have any ideas?

No, that's not my purpose, I show you part of my code to give you some clue to use the stLinkCriteria to get the dynamic result...

[beacon]

I see what your code is saying, but it appears as though the code you've written will dynamically set a different form.

I'm trying to set the current form when the Submit event is fired.

[NeoPa]

It seems like you already know how to set the RecordSource of your current form. What you're missing seems to be the call of Me.Requery afterwards to update the form to use the new RecordSource.

I'm not sure I understand what you want to do with the ControlSource of your TextBox :S

[beacon]

It seems like you already know how to set the RecordSource of your current form. What you're missing seems to be the call of Me.Requery afterwards to update the form to use the new RecordSource.

I'm not sure I understand what you want to do with the ControlSource of your TextBox :S

I need to have a control source for the textbox to enter the value on the table, right? The place where the textbox puts data will be different depending on the user choice.

If the user enters "Joe Blow" in the text box, then selects the "AFP" option button, the text string should be entered into the table "tblAuditorAFP". If the user selects "MSU, the text string should be entered into the table "tblAuditorMSU" and so forth for the "WF" option.

I thought that I had to have a control source established to enter data into a table/query.

Does this make sense?

[NeoPa]

Does this make sense?

Yes, but it's not right. Once the RecordSource changes, the Control Source will automatically be applied to the newly bound Record Source.

The only way this would not work automatically would be if the different tables had different names for the field required.

Anyway, setting the Control Source would simply involve changing Me.Control.ControlSource to the required value.

[NeoPa]

BTW Have you changed your code to make the RecordSource change work now?

[beacon]

I thought the Record Source was working, but I'm not really sure how to tell. If the only way I'll be able to tell is if the data is being entered onto the correct table, then it's not working.

I started the form without a Record Source and when the user submits the form, the Record Source should be changed accordingly and the Control Source should be changed accordingly because I don't want the annoying #Name? showing in the textbox.

I tried putting in a default Record Source and then changing it to the other, but the data still doesn't go anywhere that I can find. I hope this data doesn't wind up corrupting my database because it's lost behind the scenes.

[NeoPa]

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Please don't use any TABs in your formatted layout as this will not display correctly - Spaces only for layout.
Table Name=[tblStudent]

Field           Type        IndexInfo
StudentID       AutoNumber  PK
Family          String      FK
SName           String
University      String      FK
Mark            Numeric
LastAttendance  Date/Time

You may simply include the relevant fiels, but if unsure err on the side of including information.

[NeoPa]

I thought the Record Source was working, but I'm not really sure how to tell. If the only way I'll be able to tell is if the data is being entered onto the correct table, then it's not working.

Have you included the Me.Requery call in your code now? Tell you what, why don't you repost your current code for review.

I started the form without a Record Source and when the user submits the form, the Record Source should be changed accordingly and the Control Source should be changed accordingly because I don't want the annoying #Name? showing in the textbox.

If you start with no record source, you should also start with no control source.

Exactly how to handle it from there rather depends on your meta-data. We'll see what's needed when we have that.

[FishVal]

....
Here's the code:

Private Sub cmdSubmitAddNewUser_Click()
 
    Dim subVal As Variant
    Dim savVal As Variant
    Dim UserChoice As Variant
 
    If IsNull(Me.NewUser) Then
        MsgBox "You must enter a user name", vbOKOnly + vbExclamation, "Empty User Name"
        Exit Sub
    End If
 
    UserChoice = [optionGroup].Value
 
    If IsNull(UserChoice) Then
        MsgBox "You must select a campus", vbOKOnly + vbExclamation, "Campus Error"
        Exit Sub
    End If
 
    If UserChoice = 1 Then
        Me.RecordSource = "qryAuditorAFP"
        'for debugging purposes
        MsgBox Me.RecordSource & " me.name " & Me.Name
        Me.txtNewUser.ControlSource = "Auditor"
        MsgBox Me.NewUser.ControlSource
    ElseIf UserChoice = 2 Then
        Me.RecordSource = "qryAuditorMSU"
        Me.txtNewUser.ControlSource = "Auditor"
    ElseIf UserChoice = 3 Then
        Me.RecordSource = "qryAuditorWF"
        Me.txtNewUser.ControlSource = "Auditor"
    End If
 
    subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion, "Add User")
    If subVal = vbYes Then
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close acForm, Me.Name
        MsgBox "User was entered successfully", vbOKOnly + vbInformation, "User Added"
        savVal = MsgBox("Would you like to enter another user?", vbYesNo + vbQuestion, "Add User")
        If savVal = vbYes Then
            DoCmd.OpenForm "frmAddNewUser", acNormal
        ElseIf savVal = vbNo Then
            DoCmd.OpenForm "Switchboard", acNormal
        End If
    ElseIf subVal = vbNo Then
        MsgBox "User was not added", vbOKOnly + vbInformation, "User Not Added"
        DoCmd.CancelEvent
    End If
 
End Sub
 

Thanks, in advance, for the help

Hello, beacon.

The code will not work because as soon as you change Form.Recordsource, the form controls are being populated with values from a first record of new recordsource, hence data you've entered is lost and the first record is saved back to the table - nothing happens.

Regards,
Fish.

P.S. Do you know how to debug VBA code?

[NeoPa]

As Fish so cleverly noticed, you will need to change the record and control sources BEFORE you attempt to do anything with the data on the form.

I suggest you have a label control display the current record source. Then, if that is not the required one, the operator selects the what they want and data entry is done AFTER that.

[beacon]

Sorry I haven't gotten the chance to repost all day. I've had other emergencies to deal with.

I believe that Fish is right. I'm probably going to be better off creating 3 separate forms with the record/control sources already included and then using another form to direct the user to the correct form.

I don't want to rely on the user to enter additional users in the correct location on the table and I doubt my programming skills are sufficient enough to loop through the table to determine the next new row.

Fish, the only way I know how to truly debug is to place a msgbox strategically to find out the values of variables at certain locations. I've used the immediate window a few times with the 'print' function and I've used breakpoints a couple of times, but I don't entirely understand what I'm doing when I do it and it's never anything much more than that.

I'll check out your link though and see what I can come up with. I've used a Microsoft Press book to basically teach myself what little I know and it doesn't really delve into debugging that much, in my opinion.

Thanks for trying everybody...I was hoping that I could get away with not creating any more forms to make this work, but I'm just going to resign myself to going about it this way instead.

My ambition has hit the wall of defeat, but I'm sure I'll be back with another issue soon. This database I'm trying to create is my organization's attempt to update from old FoxPro apps and they want everything to act the same way as it did. Puts me in a pickle, as I'm sure you've noticed.

[NeoPa]

Actually the link is VERY good :-> (One of mine).

But may I suggest one last run at this from a slightly different angle.

As you are already reconciled to choosing the relevant form before opening it, how about a single form with a parameter. In the Form_Open() procedure (before the record source is processed), determine from the parameter which record source (and if necessary which control source) is required. That way, a single, slightly more clever form is used.

Benefits of this approach include the feeling that you've been clever; There are fewer forms to maintain; Adding an extra one simply involves adding some logic to the code rather than producing a whole new form (albeit very similar to pre-existing ones).