Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2003
    Posts
    17

    Question Unanswered: Changing recordset of a subform in code

    Hello all!

    I'm very new to Access in how forms relate to tables, but this forum has helped tremendously.

    My question: I have a form (don't we all) with 2 comboboxes on it and a subform imbedded within. One combobox lets the user select a computer name which then popultes the subform's textboxes with the computer's information, who it's assigned to, etc. The subform's recordsource is set to a query that grabs that info from my tables based on what was selected in the combobox. Works great.

    My problem lies in the 2nd combobox. It allows the user to select a userID which should populate the same subform with the same information, but this time based on the selected userID. I have a query built that would grab the information based on the userID, but I can't seem to code the afterupdate event of the combobox to change the subform's recordsource to the other query before refreshing (requery) the subform. I've tried several way of coding but I'm thinking now I'm going about this witht he wrong syntax:

    frmSubComputer.recordsource = qryCompEmpID

    'there isnt a "recordsource" property to set in the dropdown

    forms!frmComp.frmSubComputer.recordsource = qryCompEmpID

    'same thing, no "recordsource" property is available in the dropdown after typing "frmSubComputer."

    Am I trying to change the wrong thing? Bad syntax? Any suggestions would be super appreciated!

  2. #2
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Would you please post the db because I can't understand the problem.

    As to say: the second combo populate after the first one? Is the second combo taking info from the subform? I'm sorry I'm quite tired today, but if you explain it better I might help you.
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  3. #3
    Join Date
    Aug 2003
    Posts
    17
    Thanks for the response. I've included the dbase to help. I'l restate my question and add a new one:

    1. When I select a Computer Name, the subform's textboxes are populated with table info based on which computer name I selected. That works great. How can I get it to also populate the Employee ID combobox with the appropriate Employee ID?

    2. I would also like to select an Employee ID and have it fill in the subform and Computer Name combobox based on which Employee ID I selected. The subform's recordset is linked to a query (qryCompEmpInfo) that works based on the selection in the Computername combobox. I want to switch that link to a query (qryCompEmpID) that does the same thing, but based on the Employee ID combobox instead.

    Basically I'm trying to make the form allow me to get information about an employee and their computer by selecting either the Employee ID or the Computer Name.

    Thanks again for the help
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can set the .rowsource of a combo
    and the .recordsource of a form

    either way, if the .xxxxsource is a saved query it wants quotes:
    forms!frmComp.frmSubComputer.recordsource = "qryCompEmpID"

    izy

  5. #5
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Faster and correct...

    saved by the bell?
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  6. #6
    Join Date
    Aug 2003
    Posts
    17
    So close I can taste it!

    I tried to fix the first question about populating the Employee ID combobox with the appropriate Employee ID based on the selected computer name with the following code and I couldn't get it to work (the EmpID combo was blank after requery):

    Private Sub cboCompName_AfterUpdate()
    cboEmpID.RowSource = "qryEmpID2"
    cboEmpID.Requery
    frmSubComputer.Requery
    End Sub

    As for the Employee ID combobox, I used the following code and received an error "object doesn't support this property or method" highlighting the first line of code:

    Private Sub cboEmpID_AfterUpdate()
    Forms!frmComp.frmSubComputer.RecordSource = "qryCompEmpID"
    Me!frmSubComputer.Requery
    End Sub

    Agghhhh!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    second one first. try:
    Forms!frmComp!frmSubComputer.RecordSource = "qryCompEmpID"

    ...assuming that frmComp is the parent form and frmSubComputer is what you see in frmComp properties/other/name for the subform.

    still thinking about the first one. izy

  8. #8
    Join Date
    Aug 2003
    Posts
    17
    Sorry for troubling you on this. I've seen your help all over this board and have used a ton if it and that of others to get as far as I am so far. Thank you for this.

    As for your code I copied and pasted it in as so (can't believe I missed the 2nd '!'):

    Private Sub cboEmpID_AfterUpdate()
    Forms!frmComp!frmSubComputer.RecordSource = "qryCompEmpID"
    frmSubComputer.Requery
    End Sub

    And I'm still receiving the same error about the property not being supported. However I think you're onto something with the naming. This may sound ignorant (it does to me) but when I go to the properties box of the main form, I can't find 'Name' in the 'Other' tab. In fact I cant find my main form name in the dropdown list of the properties box at all. The only forms I find in there are 'Form' and 'frmSubComputer'. Am I looking in the wrong place or diong the wrong thing?

    /hugs to you

  9. #9
    Join Date
    Aug 2003
    Posts
    17
    Here's a shot of what I mean:
    Attached Thumbnails Attached Thumbnails sample1.jpg  

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't recognise the drop-down at the bottom of your screen, so maybe you are looking somewhere i didn't expect.

    what i had in mind was opening the parent form in design view. if the properties window (exactly like the main bit of your screen dump) is open, fine, else right-click and open it.

    then tab to Other

    then click on the main form somewhere and then the subform area ONCE to get it's attention. what do you have in name now? that's the name you have to use in
    Forms!motherName!useItHere.control.etc

    izy

  11. #11
    Join Date
    Aug 2003
    Posts
    17
    OK I understand. Yes it's named frmSubComputer as you mentioned. The screenshot was actually 2 shots puts together. The dropdown part on the bottom was a view of the dropdown (on the main part of the screenshot) opened up. Sorry for the confusion

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if the mother is "frmComp"
    and if the mother calls her subform "frmSubComputer"
    and if qryCompEmpID is a working query
    then i want
    Forms!frmComp!frmSubComputer.RecordSource = "qryCompEmpID"
    to work!!!

    my A machines are in a virus sweep & defrag mode before they go to sleep for the weekend (at least that's what my wife hopes!!) so i can't try any experiments.

    a last desperate thought:
    does qryCompEmpID return the same fields with the same names as qryCompEmpInfo?

    otherwise, no more ideas for the moment.

    izy

  13. #13
    Join Date
    Aug 2003
    Posts
    17
    No worries and no rush. There's no deadline on this project; I was just trying to figure this out after beating my head on a wall for nearly 4 days on this one

    I think the problem lies in the query itself and I think I've stumbled onto a possible solution for the first question at least. I'm about to leave out of here for the weekend and will mull this over in the meantime. After I get back on Monday and get a chance to implement some of my ideas I'll post what I found here (hopefully a solution!) and we can go from there.

    One thing I've noticed is you and your fellow Access programmers here don't get enough appreciation and recognition for what you do for us newbies So, thank you dearly for the help you've given to myself and hundreds of others!! I can't thank yuo enough. Just your feedback above has led me in new directions and I hope I can figure something out of this.

    As for the first part of your most recent post, everything is set the way you describe. The only thing in question at this point is how the queries relate to the controls and if the query is really working the way I think it is. Like I said, I'll mull it over this weekend and post what i come up with on Monday. Attached is my most recent copy of the dbase if anyone want to give it a try

    Thank you all and see you soon!
    Attached Files Attached Files

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    interesting!

    i was also not successful changing the .recordsource in the subform. i don't understand why this doesn't work and i don't really have time for experiments at the moment.

    the attached is a workaround.

    izy

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    aaaaarrrrrrggggggghhhhhhhh!!!!!

    Aaaarrgggghhhhh!

    the correct syntax is:

    Forms!MotherFormName!SubFormName.Form.RecordSource = "this"


    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •