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

[jenkag875]

Hello.



I have a main form that opens after a user enters their login. It's a tabbed navigation form. On each tab, there are various command or radio buttons. For several of those command buttons, I have it set to open a form where the end user enters an application ID number (text format) based on the parameter from the record source which is just one table.

What I'm ultimately wanting to do is a couple of things,
1.) if the application ID (field name is ApplID) doesn't exist in the database, have it display a message "that record doesn't exist, enter a different application ID" , vbretrycancel would be preference here or if they enter it correctly, it will open the form as intended.
2.) some of the forms allow more than one entry per ID so they are not distinct, but for one particular table linked to a form, it is distinct so I don't want it to allow the end user to enter in another record for the ApplID they just entered if it is already found in the table so the message would say "That application already has a High School Growth Attainment tied to it, enter a different application ID." or if they enter it correctly, it will open the form as intended.

I have tried a few times to enter the code in various places (before update of the ApplID field, on load of the form itself, etc) but every place I try it, it doesn't seem to work so I'm wondering if it's something if my syntax or with the parameter on the query within the form it doesn't allow this.

Here is the code I've tried:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[ApplID]", "frmAddDQReviewer2PersonGrowthAttainHS", "[ApplID]= '" & Me![ApplID] & "'") > 0 Then
MsgBox "That application already has a High School Growth Attainment tied to it, enter a different application ID."
Else
DoCmd.OpenForm FormName:="frmAddDQReviewer2PersonGrowthAttainHS"
End If
End Sub

Thank you for any assistance given,
Jennifer

[pbaldy]

Well, the second argument has to be the name of a table or query. It appears to be the name of a form.

[RuralGuy]

Is "frmAddDQReviewer2PersonGrowthAttainHS" the name of a query, table or form? Maybe if it was just a bit longer.

[jenkag875]

I updated it to the following and got "runtime error 2427".

Here is my updated code:

Private Sub Form_Load()
If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me![ApplID] & "'") > 0 Then
MsgBox "That application already has a High School Growth Attainment tied to it, enter a different application ID."
Else
DoCmd.OpenForm FormName:="frmAddDQReviewer2PersonGrowthAttainHS"
End If
Me.cmdSave.Enabled = False
Me.cmdCancel.Enabled = False
End Sub

[pbaldy]

On what line? What is the text of the error?

[jenkag875]

If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me![ApplID] & "'") > 0 Then

Run time error 2427, You entered an expression that has no value.

I specifically entered a bogus # when my parameter box popped up

[aytee111]

If AppID is a number data type then remove the single quotes - "[ApplID]= " & Me![ApplID])

[pbaldy]

Try

If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.ApplID & "'") > 0 Then

presuming that's the name of the textbox. If you still get an error, you should be able to go into debug mode and hover over the form reference to see what value it has.

[jenkag875]

I updated with what you entered and I'm still getting the same error. I'm not able to see the value unless I'm not doing that correctly. When the line of code was highlighted yellow, I hovered over all of the field areas and no help text displayed that I saw.

[pbaldy]

Can you attach the db here?

[Bulzie]

Before that line enter the below and put a breakpoint on it so it stops there. Hit F8 to step to next line. See what x value is.

x = DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.ApplID & "'")

Also where are you running this code? Shouldn't it be in the AfterUpdate of the Me.ApplID field on the form? So they enter a value and then you want this to kick off to see if there is an ApplID already?

[jenkag875]

This worked with no error but I think this was the other part I was wanting it to do, was give an error message if the application ID number entered wasn't found. Is it possible to include that as a nested if within this code. I don't want it to allow a duplicate entry but if they enter an application ID that isn't in the database, I need it to show them a message that the number isn't valid, enter another one.

I just tried an application ID that exists and it didn't show the message below like I wanted it to.

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

[jenkag875]

I can do that but I'll need to remove some of my data I'm testing with first.

[jenkag875]

The database is too large for me to post right now due to the weak WIFI signal where I'm at.

This is where I'm at with it currently if there are any more suggestions.

This worked with no error but I think this was the other part I was wanting it to do, was give an error message if the application ID number entered wasn't found. Is it possible to include that as a nested if within this code. I don't want it to allow a duplicate entry but if they enter an application ID that isn't in the database, I need it to show them a message that the number isn't valid, enter another one.

I just tried an application ID that exists and it didn't show the message below like I wanted it to.

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

[pbaldy]

If you mean to test the [COLOR=#333333]ApplID against a table of those, sure. Use another If/Then and a DCount() against that table. Pseudo code:

If DCount(...) > 0 Then
  If DCount(..) >0 Then
    Msgbox "duplicate"
  Else
    OpenForm
  End If
Else
  MsgBox "Bad ID"
End If