Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    MintChipMadness is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    109
    I think I know what is going on. Since the row source for the category Cbo is filtering by what is in the combo boxes above, it gives null values right at the beginning which means it won't work to take the values that way. I think by creating a new query to sort by the Directory_ID in the category cbo. then having that populate the department, division, function, and category value. Although I haven't figured out how to do that in VBA. What do you think?

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    These comboboxes are unbound? (they should be)

    The code is not to populate fields, just set a value in unbound combobox. I do it successfully.

    Ooops, think you nailed it. I knew something about this was troubling me, have dealt with this before with other posters.

    I don't think another query is needed. Instead of using reference to combobox columns in code, options:

    1. form has RecordSource that joins the two tables then use code to set combobox values by reference to the related fields in the RecordSource
    Me.cbodept = Me!Department

    2. DLookup with Directory_ID field as criteria:
    Me.cbodept = DLookup("Department","Directory","ID=" & Me.Directory_ID)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    MintChipMadness is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    109
    The only combo box that is bound is the category one.

    I did a query that sorted based on Directory_ID and it came up with one record. I just thought there might be a way to show the values in the combo boxes using that query. I didn't really know how that would happen. Maybe set the default values to the value in the query? I tried that though and it didn't work.

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I did edits on my previous post while you posted. Review again.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    MintChipMadness is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    109
    Maybe giving the row sources for the other combo boxes will help. I must be doing something wrong if it works for you.

    Department Cbo
    Code:
    SELECT DISTINCT Directory.Department FROM Directory ORDER BY Directory.Department;
    Division Cbo
    Code:
    SELECT DISTINCT Directory.Division, Directory.Department FROM Directory WHERE (((Directory.Department)=[dept])) ORDER BY Directory.Division;
    Function Cbo
    Code:
    SELECT DISTINCT Directory.Function, Directory.Department, Directory.Division FROM Directory WHERE (((Directory.Department)=[dept]) AND ((Directory.Division)=[div])) ORDER BY Directory.Function;
    Category Cbo
    Code:
    SELECT Directory.ID, Directory.Category, Directory.Department, Directory.Division, Directory.Function FROM Directory WHERE (((Directory.Department)=[dept]) AND ((Directory.Division)=[div]) AND ((Directory.Function)=[fun]));

  6. #21
    MintChipMadness is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    109
    Just saw your edits. You can have a join in the record source? You mean control source right? I always thought the only way would be to join them in a query and use the query as the source. How would I go about joining the tables in the record source?

    This is a lot of great information. I am learning a lot. I can't thank you enough.

  7. #22
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I mean the form's RecordSource. Remember post #2?

    A form or report RecordSource can be a table, query, or SQL statement. The query or SQL can include a join of multiple tables. Whether or not the resulting dataset is editable depends on the joint type and table relationships. In this case, the join is to a lookup table (as opposed to a dependent child table) and jointype would be 'Show all records from Boxes ...'

    This should allow the form to remain editable and also make the related info from Directory available for display, either in textboxes ControlSource (Locked Yes, TabStop No) or by code to populate the comboboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #23
    MintChipMadness is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    109
    Thank you. Wow that was a lot of good information. I am still really new at this and have a lot to learn.

    I wasn't sure how to write the SQL statment in the record source. I did create a query that included all the fields from the three tables though. I don't know how else to do it unless you can help with the SQL statement. I don't want the user editing the Directory table. That needs to be locked to them. I guess I can lock certain fields to edits? Is that is what you mean?

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Data controls have a Locked property. User can click on the control but cannot enter anything if it is set to yes. Also set TabStop property to no.

    Click into the RecordSource (or RowSource) property and double click the ellipses (...). This will open the query designer and can build SQL statement that will live right in the property and nowhere else.

    Should not include the BoxContents table in the form RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #25
    MintChipMadness is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    109
    Thank you so much. I got it working by doing what you said. Binding a field makes everything so much easier when cycling through the records.

    I will change this thread to solved. Thank you!! I really appreciate the help you gave me.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  2. Subform, Combo Box, Data entry
    By hellojosie in forum Access
    Replies: 2
    Last Post: 11-21-2011, 01:29 AM
  3. Help with a form data entry issue
    By kcm4491 in forum Forms
    Replies: 3
    Last Post: 11-19-2010, 12:00 AM
  4. Combo box/list for data entry.
    By geoffishere in forum Forms
    Replies: 1
    Last Post: 03-13-2010, 04:16 AM
  5. Subform Data Entry Issue
    By yuriyl in forum Forms
    Replies: 3
    Last Post: 05-14-2009, 08:49 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