Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9

    Question How to write to a table and using dlookup w/ combo boxes correctly

    I currently have a form A (tied to table A) that has a combo box, which pulls the names of certain sites (Elizabeth, NJ; Raritan Bay, etc.) via DLookup and an "On Update" event. This same form has several other fields and it displays information related to Site A, B, C and I can update those, but for whatever reason the values do not save to Table A. They will save, however, if I scrap the combo box and navigate between the sites using the Record navigation at the bottom the form (i.e. Record 1 of 50).



    Also, if I use a combination of the two, using the combo box and navigating between the Records, then Access will write to the wrong field!!! For example, if I choose Elizabeth, NJ from the dropdown, and it is normally given an autonumber of 5. If I am on the record for 1 of 37, then if I navigate to 2 of 37, then the 1 of 37 record will now also show Elizabeth, NJ in addition to record of 5/37. Is there anyway to prevent this user error from happening besides not using a dropdown/combo box?

    Am I missing something here? I would like to also make some Forms B, C, and D that reference Table A for the site names, so that there is no redundant names across tables. Tables B, C, and D have different fields and while I technically could add those columns to the first table, it would make the form way too cumbersome.

    I can add screenshots if my description isn't enough.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    When you use the record selector (at the bottom) you are clicking thru records of that record source (Table A).

    But when you select from a combobox - it may , or may not, be moving you to a record of the record source depending on how that combobox was initially set up. So it sounds like in your case that it is not.

    This much seems pretty clear cut. If you want a combobox that selects a record (so you don't have to click thru the record selector at bottom) - make a new one and be sure to follow the wizard closely in selecting the correct option.

    That seems like the first step, and then the other issues should be reviewed separately.

    hope this helps a little.

  3. #3
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by NTC View Post
    When you use the record selector (at the bottom) you are clicking thru records of that record source (Table A).

    But when you select from a combobox - it may , or may not, be moving you to a record of the record source depending on how that combobox was initially set up. So it sounds like in your case that it is not.

    This much seems pretty clear cut. If you want a combobox that selects a record (so you don't have to click thru the record selector at bottom) - make a new one and be sure to follow the wizard closely in selecting the correct option.

    That seems like the first step, and then the other issues should be reviewed separately.

    hope this helps a little.
    Yeah I guess I would need to do that then. Because I initially just went to the Create tab and chose Form. Then I right clicked on the text field that displayed the site names, converted it to a combo box, set the source to the site names, and the dropdown populated with the cities from the table.

  4. #4
    ryan1313 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    20
    So is your Combo Box still bound to the source of the form? If you want the form to be filtered to what you select in the Combo box, it should be unbound.

  5. #5
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by ryan1313 View Post
    So is your Combo Box still bound to the source of the form? If you want the form to be filtered to what you select in the Combo box, it should be unbound.
    How would I unbound it then? Would it still be writing to the first table if it was unbound? /noob question

  6. #6
    ryan1313 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    20
    You need to delete the control source under the combo box's properties. You can still set the row source to lookup the site values that you want.

    If a control is bound to a field in a table, the value shown is the value for that field that is stored for that current record. So when you are changing that combo box trying to filter your form, you are actually trying to change the value stored in the table.

    By having it unbound, it is just that. It is not bound to any record in a table, therefore you can change its value without it affecting your record.

    Edited to Add:

    I have uploaded a simple example of using a combobox to lookup info from a table on a form.

  7. #7
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by ryan1313 View Post
    You need to delete the control source under the combo box's properties. You can still set the row source to lookup the site values that you want.

    If a control is bound to a field in a table, the value shown is the value for that field that is stored for that current record. So when you are changing that combo box trying to filter your form, you are actually trying to change the value stored in the table.

    By having it unbound, it is just that. It is not bound to any record in a table, therefore you can change its value without it affecting your record.

    Edited to Add:

    I have uploaded a simple example of using a combobox to lookup info from a table on a form.
    Thanks for sharing that file. I unbounded the 4 tables in the database. Is there a way to mass DLookup information? I have about 50 fields in one of the forms and I would rather not copy paste 50 lines of DLookup queries if I can avoid it.

    I noticed you used this code instead of DLookup:
    Code:
    Dim strSQL As String
        strSQL = "SELECT * FROM tblMain WHERE [SiteID] = " & Me.cbositeLookup
        Me.Form.RecordSource = strSQL
    It's been a while since I've taken any classes involving programming languages, so I'm not sure how to decipher this except that tblMain is the table and you set the condition to match whatever the chosen SiteID is. But from looking at your attached file, it would appear that I can use this as an alternative to DLookup, no?

  8. #8
    ryan1313 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    20
    That's correct. That code sets the recordsource (the table or query that your form controls are bound to) to select all records from tblMain where the value for siteid in the table equals whatever you select in the combo box.

    I will also say that DLookup only looks up a certain value for one field. The code that I used selecs the entire record.
    Last edited by ryan1313; 08-17-2010 at 10:41 AM. Reason: Added more info

  9. #9
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by ryan1313 View Post
    That's correct. That code sets the recordsource (the table or query that your form controls are bound to) to select all records from tblMain where the value for siteid in the table equals whatever you select in the combo box.

    I will also say that DLookup only looks up a certain value for one field. The code that I used selecs the entire record.
    I'm getting a syntax error and I know it's easy to fix, but forgot how to do it.

    Option Compare Database

    Private Sub SegmentName_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT * FROM LeveeInfo WHERE [SegmentName] = " & Me.SegmentName
    Me.Form.RecordSource = strSQL
    End Sub

  10. #10
    ryan1313 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    20
    It looks ok structurally...just to make sure, your table name is 'LeveeInfo' and the field in the table that you want to filter on is 'SegmentName' and they are both spelled correctly?

  11. #11
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by ryan1313 View Post
    It looks ok structurally...just to make sure, your table name is 'LeveeInfo' and the field in the table that you want to filter on is 'SegmentName' and they are both spelled correctly?
    Yeah exactly.

    Run-time error '3075'. Syntax error (comma) in query expression '[SegmentName] = Elizabeth, Elizabeth River Left Bank South'.

    I think this fixed it. It looks like it's working. (spaced out the single and double quotes for emphasis)

    Private Sub SegmentName_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT * FROM LeveeInfo WHERE [SegmentName] = ' " & Me.SegmentName & " ' "
    Me.Form.RecordSource = strSQL
    End Sub

  12. #12
    ryan1313 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    20
    At the risk of confusing you even more, you really shouldn't be storing that whole value in your table.

    If it is possible for the same segment name to exist in multiple records, you should have a table just for segment names. In this table, you would have a unique id field set to autonumber, and then each segment name.

    In your main table, you would store the ID number and relate that number to the segment name in the segment name table. That way you are only taking up the space of a number versus having to store all of that text over and over again.

    Do a google search on access table normalization. Its sometimes a pain in the beginning, but you will be very thankful that you did as your database grows.

  13. #13
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by ryan1313 View Post
    At the risk of confusing you even more, you really shouldn't be storing that whole value in your table.

    If it is possible for the same segment name to exist in multiple records, you should have a table just for segment names. In this table, you would have a unique id field set to autonumber, and then each segment name.

    In your main table, you would store the ID number and relate that number to the segment name in the segment name table. That way you are only taking up the space of a number versus having to store all of that text over and over again.

    Do a google search on access table normalization. Its sometimes a pain in the beginning, but you will be very thankful that you did as your database grows.
    Haha I was hoping to avoid that, but I suppose it's inevitable. However, my coworker doesn't expect the database to grow too large. For what it's worth, the code worked perfectly on the first table, but once I pasted this code into the 2nd form, then all the textboxes updated to #Name? even though some, not all, of the cells were blank.

  14. #14
    ryan1313 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    20
    Make sure the controls in Form 2 (i.e. text boxes) are bound to the table in your sql statement.

  15. #15
    sergio is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by ryan1313 View Post
    Make sure the controls in Form 2 (i.e. text boxes) are bound to the table in your sql statement.
    I lost ya there, Ryan. Not sure what you mean by that. (Btw thanks alot for the help thus far lol)

    Right now, tables b, c, and d are unbound in the Control Source field under the Data tab, but they're pulling the list of locations from the first table via the Row Source field.

    SELECT LeveeInfo.SegmentName FROM LeveeInfo ORDER BY LeveeInfo.SegmentName;
    I haven't gotten around to normalizing the tables however.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-20-2010, 06:54 PM
  2. Writing data from textbox to table
    By mll in forum Forms
    Replies: 4
    Last Post: 03-10-2010, 05:10 PM
  3. Writing Access functions
    By new2access123 in forum Programming
    Replies: 5
    Last Post: 02-06-2010, 10:47 PM
  4. Automatically Writing SQL From A Table
    By smitstev in forum Programming
    Replies: 1
    Last Post: 06-05-2009, 09:38 AM
  5. I need help writing a query statement
    By dking in forum Queries
    Replies: 1
    Last Post: 02-25-2009, 09:43 AM

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