Help with dlookup or dcount - not sure what is the best option for what I need

[pbaldy]

Wow, I keep deleting the extra code tags and the forum puts them back in, splitting the code into 2 blocks. Ignore that, should all be together.

Edit: Code format fixed by Matrix.

[jenkag875]

I put this code in and the forms displays, no error message of any sort at all. I put in a bogus number again as well as a valid #.

Private Sub ApplID_AfterUpdate()
If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.ApplID & "'") > 0 Then
If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.ApplID & "'") <> 0 Then
MsgBox "That Application ID already has a High School Growth Attainment tied to it, enter a different application ID."
Else
DoCmd.OpenForm FormName:="frmAddDQReviewer2PersonGrowthAttainHS"
End If
Else
MsgBox "The Application ID entered is not in the database, enter a different Application ID."
End If
End Sub

[pbaldy]

The first test should be against whatever table holds the list of valid ID's. What is the data type of ApplID?

[jenkag875]

ApplID is text. Table tblDQPersonGrowthAttainHS is linked to one of the main tables tblDQPerson which also contains a field ApplID which is also text. That is the primary key in all of my tables when I'm joining them. There are several tables overall and some allow more than one entry per ApplID, but this tblDQPersonGrowthAttainHS only allows one.

[pbaldy]

If I understand what you're trying to do, the first DCount() should be against tblDQPerson. At that point, you're trying to be sure it's a valid ID, right? Then you check if it's already in the growth table.

[jenkag875]

Yes. I do have the common field (ApplID) in both though, so I figured I could check for it directly in the Growth Attainment table and when I change it to look at tblDQPerson for the first If statement, the results are the same.

[pbaldy]

Can you attach a db with just those tables and this form, to make it smaller?

[jenkag875]

I'm attaching as a zipped file as it said it was too large. Not sure if that will allow you to see it but I'll try that.

[pbaldy]

I don't see the code that isn't working. This worked in the immediate window:

?DCount("[ApplID]", "tblDQPerson", "[ApplID]= '1155608309'")
1

[jenkag875]

I copied from an old form, so that was my mistake.

This should be on: frmUpdateDQPersoGrowthAttainHS

Private Sub ApplID_AfterUpdate()
If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.ApplID & "'") > 0 Then
If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.ApplID & "'") <> 0 Then
MsgBox "That Application ID already has a High School Growth Attainment tied to it, enter a different application ID."
Else
DoCmd.OpenForm FormName:="frmAddDQReviewer2PersonGrowthAttainHS"
End If
Else
MsgBox "The Application ID entered is not in the database, enter a different Application ID."
End If
End Sub

[pbaldy]

I'm not sure how you expect that textbox to work in conjunction with the criteria in the source query, so I added a textbox to test with. This performed as expected:

Private Sub Text47_AfterUpdate()
    If DCount("[ApplID]", "tblDQPerson", "[ApplID]= '" & Me.Text47 & "'") > 0 Then
        If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.Text47 & "'") <> 0 Then
            MsgBox "That Application ID already has a High School Growth Attainment tied to it, enter a different application ID."
        Else
            MsgBox "opening form"
            'DoCmd.OpenForm FormName:="frmAddDQReviewer2PersonGrowthAttainHS"
        End If
    Else
        MsgBox "The Application ID entered is not in the database, enter a different Application ID."
    End If
End Sub

[jenkag875]

I inserted a text box on the form and copied your code in exactly as text box was also named Text47 and I didn't get any message to display for a valid ID entered. It's in the form header.

[pbaldy]

Are you hitting enter or tab after entering a value? Is any code running the db? If I enter 1155609007 I get the open form message box. With 1234 I get the ID not in the database message. With 1155614635 I get the duplicate message.

[jenkag875]

I have tried enter or clicking directly enter an application ID into the parameter box. I entered 111, no message, just the form was there. When I entered a valid ID, same, form just opened with entry displayed. There is just code on each form but nothing on it as a whole

[pbaldy]

I'm talking about entering a value in the new textbox, not the parameter prompt that comes up and says "Enter an application # to update the growth attainment(s) section:". That prompt is the query criteria, and filters the form to the input number.