only checks first record

[tubar]

the message box only displays if the first record in the subfrom "00upc created" contains the value 3. i would like the code to check all records in subform.


Private Sub Command220_Click()
On Error GoTo Err_Command220_Click
If Me.Form("00 upc creatED").Controls("group 2").Value = 3 Then

MsgBox ("Already created! Try Again.")

Else

DoCmd.OpenForm "00 upc create", , , ""
Forms("00 upc create").Controls("dvd release").Value = Me.[DVDID]
Forms("00 upc create").Controls("group 1").Value = 1
Forms("00 upc create").Controls("GROUP 2").Value = 3
Forms("00 upc create").Controls("PRICE").Value = Me.Text229

End If

Exit_Command220_Click:
Exit Sub

Err_Command220_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command220_Click



End Sub

 

[John Big Booty]

You could use the DCount() function to check if a record already exists.

 

[tubar]

i changed my code to this but it creates a syntax error (missing operator) in query expression 'count (group2)'.

Private Sub Command220_Click()
On Error GoTo Err_Command220_Click
If DCount("Group 2", "00 upc create", "id" = "3") Then


MsgBox ("Already created! Try Again.")
Else

DoCmd.OpenForm "00 upc create", , , ""
Forms("00 upc create").Controls("dvd release").Value = Me.[DVDID]
Forms("00 upc create").Controls("group 1").Value = 1
Forms("00 upc create").Controls("GROUP 2").Value = 3
Forms("00 upc create").Controls("PRICE").Value = Me.Text229




End If

 

[John Big Booty]

Your Syntax for the Dcount is wrong for starters, it should look something like;

If DCount("Group 2", "00 upc create", "id = 3") >0  Then
     MsgBox ("Already created! Try Again.")
     Exit Sub
Else
     DoCmd.OpenForm "00 upc create", , , ""
     Forms("00 upc create").Controls("dvd release").Value = Me.[DVDID]
     Forms("00 upc create").Controls("group 1").Value = 1
     Forms("00 upc create").Controls("GROUP 2").Value = 3
     Forms("00 upc create").Controls("PRICE").Value = Me.Text229
End If

However that hard codes the count ID = 3, I'm guessing you want that to relate to the current ID so you need something along the lines of;

If DCount("Group 2", "00 upc create", "id = " & Me.id) >0  Then
    [COLOR="Green"] 'Followed by the rest of your code here[/COLOR]

 

[John Big Booty]

Also for future reference, avoid using spaces and other special characters in Object and Control names, limit yourself to alpha and numeric characters and the under score (_) . Consider implementing a naming protocol along the lines of TBL_TableName, QRY_QueryName, FRM_ FormName, etc. This will make your code clearer to read and easier for you to write, it also makes it quiet apparent the type of Object that is being referred to in the code

 

[tubar]

i will get into the "naming" habbit...thats is great advice. i can see how it would help.

im still getting syntax error. When the user hits the button on this form, the database creates a record that will be inputted to the forms subform. This button has a value "3" which is the ID of the field "group 2" in the subform "00 upc created".i think why im creating so much trouble is that my subform "00 upc created" gets its data from table "00 upc create" while "group 2" - a field in the "00 upc created" subform gets its data from the table "group 2".

is this still the way to go?
If DCount("field", "table", "field = value") >0 Then
MsgBox ("Already created! Try Again.")
Exit Sub

 

[John Big Booty]

Yes but you will need to put actual field and table names in that match the reality of your DB.

 

[tubar]

i still get the syntax error (missing operator) in query expression 'count(group2)'.

Private Sub Command220_Click()
On Error GoTo Err_Command220_Click
If DCount("Group 2", "00 upc create", "group 2 = 3") > 0 Then
MsgBox ("Already created! Try Again.")
Exit Sub
Else


DoCmd.OpenForm "00 upc create", , , ""
Forms("00 upc create").Controls("dvd release").Value = Me.[DVDID]
Forms("00 upc create").Controls("group 1").Value = 1
Forms("00 upc create").Controls("GROUP 2").Value = 3
Forms("00 upc create").Controls("PRICE").Value = Me.Text229




End If





Exit_Command220_Click:
Exit Sub

Err_Command220_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command220_Click




End Sub

 

[John Big Booty]

Are you able to post a copy of your DB? '03 version for preference '07 otherwise.

Also when posting code please wrap it in the Code tag using the "#" at the top of the posting window.