Update a field in subform1 based on criteria of SubForm2

[Khalil Handal]

Hi
SubForm1 and SubForm2 (based on queries) are two Sub Forms on a Main Form. Each record in SubForm1 shows related records in Subform2.
A Yes/No field is on SubForm1 with default value= Yes. I want to write criteria for the Yes/No field BeforUpdate Event so that it will not to be updated (chnaged from Yes to No) if records exists in subForm2.
How can this be done?

KHalil

[Welshgasman]

Use a Dcount() ?

[Khalil Handal]

Hi,

I used this line code:

If DCount("*", "queryNameOfSubForm2", "[ContainerNameSubForm2].[pkNumberId]=fkNumberId") > 0

And had a Type mismatch error.
Khalil

[Welshgasman]

You need to concatenat the control value.
You also need to adress the control correctly depending on where the code is placed.
http://access.mvps.org/access/forms/frm0031.htm

[pkNumberId]= " & fkNumberId

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.


Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

[Khalil Handal]

Hi,
I used the following:

If DCount("*", "queryNameOfSubForm2", "[ContainerNameSubForm2].[pkNumberId]= " & fkNumberId) > 0

I got the error message: Variable not defined.

I added:

Dim fkNumberId As Integer

the error is: Typemismatch
Any ideas

Khalil

[Welshgasman]

Yes, pknumber is not a numeric