By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,390 Members | 1,554 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,390 IT Pros & Developers. It's quick & easy.

Dynamically change record source/control source on a form

beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSubmitAddNewUser_Click()
  2.  
  3.     Dim subVal As Variant
  4.     Dim savVal As Variant
  5.     Dim UserChoice As Variant
  6.  
  7.     If IsNull(Me.NewUser) Then
  8.         MsgBox "You must enter a user name", vbOKOnly + vbExclamation, "Empty User Name"
  9.         Exit Sub
  10.     End If
  11.  
  12.     UserChoice = [optionGroup].Value
  13.  
  14.     If IsNull(UserChoice) Then
  15.         MsgBox "You must select a campus", vbOKOnly + vbExclamation, "Campus Error"
  16.         Exit Sub
  17.     End If
  18.  
  19.     If UserChoice = 1 Then
  20.         Me.RecordSource = "qryAuditorAFP"
  21.         'for debugging purposes
  22.         MsgBox Me.RecordSource & " me.name " & Me.Name
  23.         Me.txtNewUser.ControlSource = "Auditor"
  24.         MsgBox Me.NewUser.ControlSource
  25.     ElseIf UserChoice = 2 Then
  26.         Me.RecordSource = "qryAuditorMSU"
  27.         Me.txtNewUser.ControlSource = "Auditor"
  28.     ElseIf UserChoice = 3 Then
  29.         Me.RecordSource = "qryAuditorWF"
  30.         Me.txtNewUser.ControlSource = "Auditor"
  31.     End If
  32.  
  33.     subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion, "Add User")
  34.     If subVal = vbYes Then
  35.         DoCmd.RunCommand acCmdSaveRecord
  36.         DoCmd.Close acForm, Me.Name
  37.         MsgBox "User was entered successfully", vbOKOnly + vbInformation, "User Added"
  38.         savVal = MsgBox("Would you like to enter another user?", vbYesNo + vbQuestion, "Add User")
  39.         If savVal = vbYes Then
  40.             DoCmd.OpenForm "frmAddNewUser", acNormal
  41.         ElseIf savVal = vbNo Then
  42.             DoCmd.OpenForm "Switchboard", acNormal
  43.         End If
  44.     ElseIf subVal = vbNo Then
  45.         MsgBox "User was not added", vbOKOnly + vbInformation, "User Not Added"
  46.         DoCmd.CancelEvent
  47.     End If
  48.  
  49. End Sub
  50.  
Thanks, in advance, for the help
Sep 30 '08 #1
Share this Question
Share on Google+
15 Replies


P: 26
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...
Sep 30 '08 #2

beacon
100+
P: 579
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?
Sep 30 '08 #3

P: 26
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...
Sep 30 '08 #4

beacon
100+
P: 579
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.
Sep 30 '08 #5

NeoPa
Expert Mod 15k+
P: 31,661
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
Sep 30 '08 #6

beacon
100+
P: 579
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?
Sep 30 '08 #7

NeoPa
Expert Mod 15k+
P: 31,661
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.
Sep 30 '08 #8

NeoPa
Expert Mod 15k+
P: 31,661
BTW Have you changed your code to make the RecordSource change work now?
Sep 30 '08 #9

beacon
100+
P: 579
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.
Sep 30 '08 #10

NeoPa
Expert Mod 15k+
P: 31,661
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]
Expand|Select|Wrap|Line Numbers
  1. Field           Type        IndexInfo
  2. StudentID       AutoNumber  PK
  3. Family          String      FK
  4. SName           String
  5. University      String      FK
  6. Mark            Numeric
  7. LastAttendance  Date/Time
You may simply include the relevant fiels, but if unsure err on the side of including information.
Oct 1 '08 #11

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 1 '08 #12

FishVal
Expert 2.5K+
P: 2,653
....
Here's the code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSubmitAddNewUser_Click()
  2.  
  3.     Dim subVal As Variant
  4.     Dim savVal As Variant
  5.     Dim UserChoice As Variant
  6.  
  7.     If IsNull(Me.NewUser) Then
  8.         MsgBox "You must enter a user name", vbOKOnly + vbExclamation, "Empty User Name"
  9.         Exit Sub
  10.     End If
  11.  
  12.     UserChoice = [optionGroup].Value
  13.  
  14.     If IsNull(UserChoice) Then
  15.         MsgBox "You must select a campus", vbOKOnly + vbExclamation, "Campus Error"
  16.         Exit Sub
  17.     End If
  18.  
  19.     If UserChoice = 1 Then
  20.         Me.RecordSource = "qryAuditorAFP"
  21.         'for debugging purposes
  22.         MsgBox Me.RecordSource & " me.name " & Me.Name
  23.         Me.txtNewUser.ControlSource = "Auditor"
  24.         MsgBox Me.NewUser.ControlSource
  25.     ElseIf UserChoice = 2 Then
  26.         Me.RecordSource = "qryAuditorMSU"
  27.         Me.txtNewUser.ControlSource = "Auditor"
  28.     ElseIf UserChoice = 3 Then
  29.         Me.RecordSource = "qryAuditorWF"
  30.         Me.txtNewUser.ControlSource = "Auditor"
  31.     End If
  32.  
  33.     subVal = MsgBox("Are you sure you want to submit?", vbYesNo + vbQuestion, "Add User")
  34.     If subVal = vbYes Then
  35.         DoCmd.RunCommand acCmdSaveRecord
  36.         DoCmd.Close acForm, Me.Name
  37.         MsgBox "User was entered successfully", vbOKOnly + vbInformation, "User Added"
  38.         savVal = MsgBox("Would you like to enter another user?", vbYesNo + vbQuestion, "Add User")
  39.         If savVal = vbYes Then
  40.             DoCmd.OpenForm "frmAddNewUser", acNormal
  41.         ElseIf savVal = vbNo Then
  42.             DoCmd.OpenForm "Switchboard", acNormal
  43.         End If
  44.     ElseIf subVal = vbNo Then
  45.         MsgBox "User was not added", vbOKOnly + vbInformation, "User Not Added"
  46.         DoCmd.CancelEvent
  47.     End If
  48.  
  49. End Sub
  50.  
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?
Oct 1 '08 #13

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 1 '08 #14

beacon
100+
P: 579
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.
Oct 1 '08 #15

NeoPa
Expert Mod 15k+
P: 31,661
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).
Oct 2 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.