Page 3 of 16 FirstFirst 12345678910111213 ... LastLast
Results 31 to 45 of 238
  1. #31
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The lookup field means the value is actually numeric, plus you had a parentheses out of place. Try



    If (Me.BOARunResults = 2 Or Me.BOARunResults = 3) And Nz(Me.BOAOperator, "") = "" Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #32
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by pbaldy View Post
    The lookup field means the value is actually numeric, plus you had a parentheses out of place. Try

    If (Me.BOARunResults = 2 Or Me.BOARunResults = 3) And Nz(Me.BOAOperator, "") = "" Then
    That did the trick! Thank you. Could you please explain some more what you mean by "The lookup field means the value is actually numeric"? I'm following that you used 2 and 3 based off the ID numbers for the selection, but why does it use that field and not the BOARunResultes field?

  3. #33
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

    The Evils of Lookup Fields in Tables
    Contributors
    Arvin Meyer
    Joan Wild

    1. A Lookup field in a table displays the looked-up value. For instance, if a user opens a table datasheet and sees a column of company names, what is in the table is, in fact, a numeric CompanyID, and the table is linked with a select statement to the company table by that ID.
    2. Any query that uses that lookup field to sort by that company name won't work. Nor will a query that uses a company name in that field as a criteria. If a user creates a combobox to select the company using a value list, the data in the table can be over-written.
    3. Another relationship is created which then creates another set of indexes when a Lookup field is created, thus bloating the database unnecessarily.
    4. If a combobox based on the lookup is used in a form, and a filter is applied, the persistent filter effect of Access often saves the filter and the next time the form is opened, there will be a prompt for the value (which cannot be provided, thus creating an error).
    5. Reports based on the lookup field need a combobox to display the data, causing them to run more slowly. The underlying recordsource can also be modified to include the table, however the index, (unless it was set up within a proper relationship) may not be optimized.
    6. Lookup fields mask what is really happening, and hide good relational methodology from the user.
    7. The database cannot be properly upsized to, or queried by, another engine (without removing all the lookup fields) because no other engines use or understand them.
    8. If security is implemented, permissions to tables is usually denied, and RWOP queries are used for data access. There will often be errors that there are no permissions on a specific table that isn't even being used in a query (because the lookup field is). If the queries are nested or complex, it can take some time to track down the lookup that's causing the error (that is, if it occurs to you).
    See in red above.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #34
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It was the first thing listed on the link Gicu posted regarding lookup fields. You "see" the text value in the table, but it's actually storing the numeric value. It can be very confusing (as you've seen) and one of the reasons most of us avoid lookup fields.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #35
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Aright, I see that now. So how would I do what I'm trying to do without using lookup fields?

  6. #36
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just use a combo box on the form.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #37
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    When I add a combo box on the form, I've been using the option to get the values from a table or should I be typing in the values I want?

  8. #38
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    No, but don't use the lookup feature in the table when you define your fields. You can (and should) use values from a table or query in the combo's row source, but with no looukups to mess you around it is easy to see what is being stored/displayed. For example to show a name but store an ID simply set the combo to have 2 column, set their widths to 0",2" and set the Bound Column to 1 and it will show you the name but store the ID (RowSource: "SELECT ID,FULL_NAME FROM tblYourTable Order By FULL_NAME;").

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #39
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    I'm not sure I'm following how that will work. Let's take my Event name combo box on the RunSheet form as an example. You are saying that I should have a table containing the list of Events that the operators can select from, but don't use a lookup? Just enter the RowSource code? I build all of my combo box with the Design view Combo box tool, does that put the lookups in there?

  10. #40
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    That one (Event on the top part of the form) is OK I am talking about your table fields:
    Click image for larger version. 

Name:	BOA.PNG 
Views:	48 
Size:	65.1 KB 
ID:	41517
    This should not have the lookup set to Combo box but left to the default Textbox in the table design. Then when you build a form you change the text box to a combo box and set its properties (row source, row source type, column count, column widths, bound column) to fit your needs.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #41
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Okay, I think I'm getting. For the BOARunResults you have shown above, in the RunResultData table I changed the Display Control type from Combo Box to Short Text. Access prompted me that it was doing to remove indexes (which I think is referring to how it is being used on my RunSheet form). I told it okay and then opened my RunSheet form and deleted the original combo box I had for BOA Run Results. I then put a new combo box in it's place and clicked on the Row Source builder, selected the BOAResults table and added the BOARunResults field. Now when I click on BOA Run Results combo box on the RunSheet form, I see my three options listed there. In order for that selection to be saved to the RunResultData table, I would need to make the Control Source BOARunResult via the dropdown arrow in its property sheet.

    Hopefully that is all correct. But now, how do I know which of my combo boxes need to be made without lookups and which ones are okay as lookups (like the Event one)?

  12. #42
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Your last sentence is wrong, there are no combo boxes that need to be made with lookups in tables. All lookup fields should be avoided in the tables as there should not be any data entry allowed in a table or query. In a form you can have combo boxes bound to a field from one table (via the control source property) that is the record source for the entire form and the same combo set up to provide the "choices" from a second table (usually called lookup tables) via the row source property.
    https://www.techonthenet.com/access/...oxes/index.php
    http://www.fmsinc.com/microsoftacces...xes/index.html

    Hopefully I was able to explain it a bit better now.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #43
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Alright, I think I got it now. I've gone back and removed all of the table lookups and got everything form entry wise working with out them.

  14. #44
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Okay, I have another question for you guys regarding this topic. How would I code the following: If SBIRSRunResult = Nominal or Off Nominal, then they have to select a name for any of the fields SBIRSWorkstation1 through 5? It does not matter which workstation the select a name, but at least one of them has to have a name selected.

    The first part of the If statement seems like it could be reused, it is just the And portion that I need rework?

  15. #45
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you don't care which one they choose you could check for the Len(NZ(SBIRSWorkstation1,"") & Nz(SBIRSWorkstation2,"") & ......NZ(SBIRSWorkstation5,"")>0 'at least one was entered.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 4
    Last Post: 10-13-2014, 09:20 AM
  2. Replies: 6
    Last Post: 02-19-2014, 11:11 AM
  3. Replies: 3
    Last Post: 07-03-2013, 10:38 AM
  4. Replies: 1
    Last Post: 10-30-2012, 10:29 AM
  5. Replies: 1
    Last Post: 07-11-2012, 08:36 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