Problem with referencing fields

[Kevin_S]

Problem with record in 1-to-many

Hi Everyone -

I have a form that has multiple tabs on it with each tab containing a different subform. One subform deals with ADA compliance and has a locked checkbox field that is to show when all aspects of compliance are met called chkFullComp. Basically, there are 6 individual fields across 3 subforms that must be checked in order for the main full compliance box to be checked. The code to check for this is done with the 'On Current' event of the main form called frmMain.

I basically used an If - And - Then - Else statement to search throught the other subforms and see if each record meets the standard for full compliance. If each field mets the standard then the code fills in the chkFullComp box. Simple enough....

Now - the problem that I have run into is that two of the subforms are built on 1 to many relationships with the main form since they can contain more that one record for each site. The problem that occurs is that those sites where there are more than one record if at least one of the linked records meets ADA standards then that is ok for Full compliance HOWEVER, the code fails to recognize the other records and only looks at the first record SO it tells me that site isn't compliant when i can see in the subform it is (viewing subform as datasheet). QUESTION - how do I make it check the other records in the 1 to many relationship?

DO I need a loop? (Not real familar with its use)

Thanks in advance for any and all help on this!

Kevin

 

[Kevin_S]

Thanks for the reply Pat.

I tried to use the snipet of code you provided as an example and manipulate it to fit my code but I keep getting an error that says:

Compile error:
Expected: list seperator or )

and the value I am searching for ("YES") is highlighted. The following is the code sample with my field plugged in:

If Not IsNull(DLookup("HC_plate","tblSkidPier","SITEID = " & Me.SITEID & " AND HC_plate = "YES")) Then

Now the DLookup is looking at the field 'HC_plate' in the table where it resides(tblSkidPier) where the 'SITEID' (primary Key) is equal to that of the main form AND where the field 'HC_plate' has a value of "YES". Is this the correct code for this? sorry for have to ask this again but I'm not too familiar with DLookUp's nested in If-Then statements.

Thanks for all your help Pat... I know from reading your posts on this board your a busy person.

Thank you,
Kevin

 

[DJN]

Try putting single quotation marks around the text part e.g.

If Not IsNull(DLookup("HC_plate","tblSkidPier","SITEID = " & Me.SITEID & " AND HC_plate = 'YES'")) Then

 

[Kevin_S]

Hi everyone,

I still am not getting the result I was looking for (or it is very possible that I am I'm just not working with the result the proper way)

Is there any suggestion as to the work around for this?

Thanks in advance for the help!
-Kevin

 

[Kevin_S]

Hi Pat,

Thanks for the continued assistance I appreciate it.

I tried to step through the code as you suggested but whenever I put a break in the code and try to use Debug.print I am asked to select the macro to which this code applies BUT this is code behind form so I tried to move it to a new module BUT that doesn't work as the code is dependent on data on the form! UGHH!!!

Here is the code I am working with that you provided to assist me:

If Not IsNull(DLookup("HC_plate", "tblSkidPier", "SITEID = " & Me.SITEID & " AND HC_plate = 'YES'")) Then
'DO what I want
'else
'DO something else

any suggestions as to how I can make this work so I can at least debug it?

Thanks Pat
Kevin

 

[Tay]

Should there be an apostrophe in front of the Else?

 

[Kevin_S]

Hi Tay,

The apostrophe in front of else in the statement above was just an example of the way the code is set up and is not the actual code itself. I just put the 'else' and the 'Do something' and 'Do something else' as examples under the actual If-Then statement to show direction. Sorry if that was confusing but I appreicate the attempt to provide assistance!


Thanks,
Kevin

 

[Rich]

Is yes from a Yes/No field or a text value?

 

[Kevin_S]

Hi Rich,

'YES' is a text value. If you think having 'Yes' as a yes/no field value would work I could redo that field. If you have a suggestion let me know.

Thanks to everyone who is helping out!

Kevin

 

[Kevin_S]

Hi Pat,

I am trying to run the code you just provided but I keep getting asked for the name of the macro and I am not running the code from a macro...?

Any suggestions as to how I can run the code without having to worry about this?

Thank again - you have been a huge help!
Kevin

 

[Kevin_S]

Success!!!!!

I was able to trap the error and fix the problem Huray!!!!

Thanks to all of you who have helped me on this I really appreciate it

and... Thanks in particular to you Pat for taking the time to help a novice coder...

Thanks again,
Kevin