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
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?
See in red above.
The Evils of Lookup Fields in Tables
Contributors Arvin Meyer Joan Wild
- 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.
- 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.
- Another relationship is created which then creates another set of indexes when a Lookup field is created, thus bloating the database unnecessarily.
- 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).
- 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.
- Lookup fields mask what is really happening, and hide good relational methodology from the user.
- 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.
- 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).
Cheers,
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.
Aright, I see that now. So how would I do what I'm trying to do without using lookup fields?
Just use a combo box on the form.
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?
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,
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?
That one (Event on the top part of the form) is OK I am talking about your table fields:
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
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)?
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,
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.
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?
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