DCount Function referencing Current Records Only 1

[LarryDavidow]

I have a table that has two fields combined to form a Key field. I want to use the Dcount function to customize a msgbox when the user enters a value that already exists for that record in the subform.

This is what I have so far...

Dim intCt As Integer
intCt = DCount("[ModCode]", "CastingDetail", "[ModCode] = '" & Me.Modcode & "'&quot
intCt = DCount("[CastingDetailCode]", "CastingDetail", "[CastingDetailCode] = " & Me.CastingDetailCode & "&quot + intCt
If intCt <> 0 Then
MsgBox &quot;You have already selected &quot; & Me.Combo10.Column(1) & &quot; for this casting&quot;
Me.Combo10 = &quot;&quot;
Exit Sub
End If

I think I'm going about this the wrong way. The Dcount function will work on the records in the subform, however, I need to make the statement specific to the records that are currently open in the subform and not the entire table. Make any sense??? I hope so. Please help!!!

 

[RickSpr]

If you're using the subform in the normal way, there are linking fields specified in the subform control's Link Master Fields and Link Child Fields. You need to create a criterion that specifies the linking field(s) in the subform is equal to the linking field(s) in the main form, and add that to the last parameter of the DCount()s.

For example, if the linking field in both the main form and the child form is named OrderID, your first statement would be:

    intCt = DCount("[ModCode]", "CastingDetail", "[ModCode] = '" & Me.Modcode & "' AND OrderID = " & Me.Parent.OrderID)

The &quot;Me.Parent.OrderID&quot; refers to the OrderID field in the subform's Parent form, which is the main form.
(If OrderID is not numeric, of course, you need to surround it with apostrophes.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[LarryDavidow]

Hi Rick

I am getting an error with your code. &quot;Run-time error '2465'
Application-defined or object-defined error.

My linking field is ModCode. I want to show all ModCodes where ModCode AND CastingDetailCode are unique. In other words, if Dcount(ModCode AND CastingDetailCode) is <> 0, then MsgBox.

Here is the code of yours I tried that generated the error.

intCt = DCount(&quot;[CastingDetailCode]&quot;, &quot;CastingDetail&quot;, &quot;[CastingDetailCode] = &quot; & Me.CastingDetailCode & &quot; and ModCode = &quot; & Me.Parent.Modcode)

 

[RickSpr]

I think I may have misunderstood you to begin with, since you didn't explain which fields were in which table or how the tables were related or what their keys were or where your code was running. I had to guess about all that, and I guessed wrong. Take a look at the FAQ in my signature for some tips on what information to provide when you post a question.

I can infer a little more from your latest post. It now seems to me that the main form is bound to a table whose key is ModCode, and the subform is bound to a table whose key is ModCode and CastingDetailCode. Combo10 is an unbound combo box on the main form that lists casting detail codes. When the user selects an item from Combo10, you create a new row in the subform's table (CastingDetail) for the selected casting detail code and the main form's ModCode. Before you create the new row, you want to verify that there isn't already a row with that key.

If these inferences are correct, you can use this:

    DCount("[CastingDetailCode]", "CastingDetail", "[ModCode] = '" & Me.ModCode _
        & "' AND [CastingDetailCode] = '" & Me.Combo10 & "'")

Your original code was nearly right, but it summed the matches on either ModCode or CastingDetailCode. If my inference is correct, you only want to know if both of them match, which is what the criteria expression in this function call tells you.

BTW, &quot;Application-defined or object-defined error&quot; just means that you asked VBA for the description of an error code it doesn't know. Error 2465 is defined in Access, so you have to get the description using the function AccessError(2465). That description is &quot;Microsoft Access can't find the field '|' referred to in your expression.@You may have misspelled the field name, or the field may have been renamed or deleted&quot;. It probably occurred because I assumed your code was running in the subform, but it's really running in the main form and CastingDetailCode doesn't exist there.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[LarryDavidow]

Thank you Rick.

This is what worked.

Dim intCt As Integer
intCt = DCount(&quot;[CastingDetailCode]&quot;, &quot;CastingDetail&quot;, &quot;[ModCode] = '&quot; & Me.Combo17.Column(0) & &quot;' AND [CastingDetailCode] = &quot; & Me.CastingDetailCode & &quot;&quot
If intCt > 0 Then
MsgBox &quot;You have already selected &quot; & Me.Combo17.Column(1) & &quot; for this casting&quot;
Me.Combo17 = &quot;&quot;
Exit Sub
End If

You definitely helped me get there...

As for not posting my problem clearly, if I understood the problem in its etirety, I would be able to post a more concise description of what the problem is. If I understand the problem, then I sould be able to solve it myself. You are always going to find people that don't post their problems the way you do. That's because they are still learning things that you have forgotten already!

I tried to explain my problem in the best way I could see fit. The fact that you actually could figure out exactly what I was trying to do tells me that you are a true expert at what you do.

I guess what happened is exactly what was supposed to happen... a person posted a problem, and an expert helped them out.

Thank you again for helping me. I am very grateful!

Larry

 

[RickSpr]

Thank you for the star, Larry.

I was really just trying to get across that anytime you're describing a problem, it helps to give the names of all the relevant objects and the relationships (in the ordinary sense) between them. It's better to give too much information than too little. Conciseness is not a virtue when you don't know what's important and what isn't.

I wasn't asking you to understand the problem, I was asking you to tell me what you knew. There wasn't anything in my second paragraph above that you didn't know, but I needed to know it. (Except that I guessed wrong about some of it, but fortunately you were able to get the gist of it.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein