Results 1 to 5 of 5
  1. #1
    MXQ's Avatar
    MXQ is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    2

    Comboboxes

    I have almost a similar issue, except that the two comboboxes in question, aren’t on the same form, so I hope you don’t mind if I hijack this thread.

    In my case there are two forms based on two different tables (supervisors and clients - in 1:n relation. Supervisor.ID -> Clients.FID).


    The supervisor table has fields like e.g. Client1, Client2, Client3 etc.

    The supervisor form has comboboxes to choose clients from and vice versa.

    The goal is that whenever I chose a supervisor in the combobox of the clients form, it should adjust the client’s name in the combobox of the supervisor’s form. But it doesn’t.
    Click image for larger version. 

Name:	clientform.png 
Views:	22 
Size:	33.7 KB 
ID:	42567 Click image for larger version. 

Name:	supervisorform.png 
Views:	22 
Size:	37.0 KB 
ID:	42568



    I tried it as follows (using the clients’ form):



    Private Sub cboSupervisor_AfterUpdate()
    Dim DB As Database
    Dim rs As Recordset
    Dim strSQL As String
    Set DB = CurrentDb()
    strSQL = "SELECT * FROM Supervisors WHERE ID = Me!cboSupervisor"
    Set rs = DB.OpenRecordset("Supervisors", dbOpenDynaset)
    rs.Edit
    rs!Client1 = DLookup(“Person”, “Clients”, “FID = “ & Me!cboSupervisor)
    rs.Update


    rs.Close
    Set rs = Nothing
    End Sub

    Nothing happened but when I replace rs.Edit with rs.AddNew it will add a correct new record, so why can’t I edit it the same way?

  2. #2
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,971
    First a question: Is FID a Number...that's the way it's currently coded.

    Secondly, in your statement below...was it copied directly from your code?

    rs!Client1 = DLookup(Person, Clients, FID = & Me!cboSupervisor)

    That's the only line, in your code, where you use the funky quotation marks in red. Access VBA doesn't tolerate these...you need to replace them with the 'normal' ones found in the rest of your code.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,330
    If you're wanting a combo list to reflect what's chosen in some other combo, that's typically known as cascading combos, which you could research to see how to adapt for your needs. I've never seen anyone asking how to do that on different forms though, and I have a hard time following your explanation. Maybe you want the value in the "one" side combo to be a value that's related to what has been chosen in the "many" side and the goal has nothing to do with filtering a list. I'd advise to forget that and just do things in the normal fashion as I'd describe that as the tail wagging the dog. I also suspect your tables are not properly normalized if you have designed fields where each value of an attribute has its own field (client1, client2, etc.).

    Unless a moderator moved your post, you seem to have started your own thread here. Does that indicate that you copied your post from another forum?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    MXQ's Avatar
    MXQ is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2020
    Posts
    2
    Quote Originally Posted by Missinglinq View Post
    First a question: Is FID a Number...that's the way it's currently coded.

    Secondly, in your statement below...was it copied directly from your code?

    rs!Client1 = DLookup(Person, Clients, FID = & Me!cboSupervisor)

    That's the only line, in your code, where you use the funky quotation marks in red. Access VBA doesn't tolerate these...you need to replace them with the 'normal' ones found in the rest of your code.

    Linq ;0)>
    Hello Missinglinq and thank you for your time. First, FID is the foreign ID, the supervisor's ID in the client table.
    Secondly, I just wrote it down from my memory, didn't copy it from the code. The actual code hasn't any quotation marks as there aren't any spaces between words (Person, Clients, etc.)

    Quote Originally Posted by Micron View Post
    If you're wanting a combo list to reflect what's chosen in some other combo, that's typically known as cascading combos, which you could research to see how to adapt for your needs. I've never seen anyone asking how to do that on different forms though, and I have a hard time following your explanation. Maybe you want the value in the "one" side combo to be a value that's related to what has been chosen in the "many" side and the goal has nothing to do with filtering a list. I'd advise to forget that and just do things in the normal fashion as I'd describe that as the tail wagging the dog. I also suspect your tables are not properly normalized if you have designed fields where each value of an attribute has its own field (client1, client2, etc.).

    Unless a moderator moved your post, you seem to have started your own thread here. Does that indicate that you copied your post from another forum?
    Hello Micron, thank you for your time and sorry for the hard time following my explanation. Yes, my post was moved from here: https://www.accessforums.net/showthread.php?t=81359
    I posted it there because orange mentioned the cascading combos and I thought it could be useful in my case.
    Could you provide me a hint on how you would accomplish the following:

    You have a client profile where you could chose a supervisor from a combobox. So far so good but when I open the supervisor profile I would like to see the just assigned client there without having to add it manually.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,013
    Sorry for confusion re this post -- I moved it to its own thread in Forms because MXQ had mentioned hijacking the original. I should have made a comment when moving the post.

    I agree with Micron that it's unusual to see the combos on different forms.
    It might be helpful if you could post your table designs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-31-2016, 12:16 PM
  2. Validation of the comboboxes
    By dr223 in forum Access
    Replies: 1
    Last Post: 01-13-2012, 07:31 AM
  3. Cascade of 3 or more ComboBoxes
    By tomullus in forum Forms
    Replies: 4
    Last Post: 10-08-2011, 06:22 PM
  4. Comboboxes go where?
    By PaulCW in forum Database Design
    Replies: 12
    Last Post: 10-04-2011, 02:34 AM
  5. Subform with Comboboxes
    By Angate in forum Forms
    Replies: 5
    Last Post: 04-23-2010, 08:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums