simple save command ???

[CEH]

Trying to do something here on a form. Very simple, but I don't know why it is not working.
Have a form, First input is a Job Number, this field is a primary key. When you tab to the next field it is not coming up with an error on duplication. I'm not sure why. I put in a Docmd.save on "after update, then lost focus..... But apparently it is not saving when you tab off of this field. Simple answer????????

 

[RV]

CEH,

state a bit more clearly what you're trying to achieve.

RV

 

[Captain Chaos]

docmd.save is used to save an object (query, report,Form etc). You seem to be adding values into controls and thinking that you can docmd.save. Not so. All you are doing is keep saving the form. The subject is more complex than that. Keep taking the lessons.

 

[ghudson]

The record will not be saved until the user clicks a command button to save the record or the user moves to another record or the user requeries the record source [and I am sure there are other events that will do it but those are a fow of the common ones].

If you want to prevent duplicate Job Numbers then you should test the value in that field with the records in the table. You can use the Dlookup() function to do and give the user a kind warning message if the number already exists.

Search the forum for your quest has been asked and answered in few hundred threads within the forum.

This command will save the current record...

docmd.RunCommand acCmdSaveRecord

 

[CEH]

well...........

Your onto what I am looking for..... I was looking at the save command simple to validate if the ID had bben used...... Not the best way I see...... Saw this code

Private Sub Jobs_JobNumber_AfterUpdate()
If DCount("[Jobs_JobNumber]", "Jobs", "[Jobs_JobNumber] = '" & [Forms]![frmNewJob]!Jobs_JobNumber "'") > 0 Then
MsgBox "That customer number already exists.", vbInformation, "User ID Already Exists"
Exit Sub
Else
'for testing if false
End If
But getting error....... "expected list seperator" ???????

Know what the problem is?
PS.... The Job numbers are both letters and numbers

 

[ghudson]

I think you are missing an ampersand, try this...

If DCount("[Jobs_JobNumber]", "Jobs", "[Jobs_JobNumber] = '" & [Forms]![frmNewJob]!Jobs_JobNumber & "'") > 0 Then

 

[Pat Hartman]

FYI - Docmd.save - saves the form NOT the current record. To save the current record use - DoCmd.RunCommand acCmdSaveRecord

 

[CEH]

Almost there........

OK, Yes it works fine now... Almost... Still one problem. After you click "OK" to the error message the cursor moves to the next field and still allows you to input other info. I am trying to get it to stay on the job number field until a valid number is input.

 

[ghudson]

You need to set the focus back to that field.

Me.YourTextBoxName.SetFocus

 

[CEH]

Well............again

OK..... Understand the set focus. Would I put the line in the same IF statement? or in the Else? Or a different statement.......
But.... first off...... IT still doesnt work... With this vba it gives the correct error message...... but with EVERY job number you input! Doesnt matter if it has been used or not!

 

[ghudson]

Then your Dcount is not correct. Post your code for the after update event for that text box. List the name of the text box, the name of the table and the name of the field in question within the table. Your text box and the name of the table field can not be the same. The name of your text box should have a txt prefix. Like txtJobNumber.

 

[CEH]

code

If DCount("[Jobs_JobNumber]", "Jobs", "[Jobs_JobNumber] = '" & [Forms]![frmNewJob]!Jobs_JobNumber & "'") > 0 Then
MsgBox "That customer number already exists.", vbInformation, "User ID Already Exists"
Exit Sub
Else
'for testing if false
End If

I can see I need to start naming things better, the importance of prefixes becomes clear after getting into VBA
Field is "JobNumber"
Text box is "Jobs_JobNumber"
Table is "Jobs"

I'm guessing that one of the "Job_JobNumber" after the "DCount" should be "JobNumber" ........ Which place is the field and which is the Text box?

 

[ghudson]

If DCount("[Jobs_JobNumber]", "Jobs", "[Jobs_JobNumber] = '" & [Forms]![frmNewJob]!Jobs_JobNumber & "'") > 0 Then
MsgBox "That customer number already exists.", vbInformation, "User ID Already Exists"
Exit Sub
Else
'for testing if false
End If

I can see I need to start naming things better, the importance of prefixes becomes clear after getting into VBA
Field is "JobNumber"
Text box is "Jobs_JobNumber"
Table is "Jobs"

I'm guessing that one of the "Job_JobNumber" after the "DCount" should be "JobNumber" ........ Which place is the field and which is the Text box?

I agree.

Try

If DCount("[JobNumber]", "Jobs", "[JobNumber] = '" & [Forms]![frmNewJob]!Jobs_JobNumber & "'") > 0 Then

 

[CEH]

Thanks

OK..... That seems to work fine. Code is now....
Private Sub Jobs_JobNumber_AfterUpdate()
If DCount("[JobNumber]", "Jobs", "[JobNumber] = '" & [Forms]![frmNewJob]!Jobs_JobNumber & "'") > 0 Then
MsgBox "That customer number already exists.", vbInformation, "User ID Already Exists"
Me.Jobs_JobNumber.SetFocus
Exit Sub
Else
'for testing if false
End If
End Sub
I added the SetFocus as mentioned above....... Does take the cursor back to the JobNumber field....... Only problem is still you can then tab off the field and get no error message......... ideas? I am thinking I need it to loop thru the dcount until a number that does not exist is input. Correct?

 

[ghudson]

You also need to add the code to the forms BeforeUpdate event. If true then you cancel the event to prevent the user from saving the record. Search around for there are other examples of how to valid the data and canceling the BeforeUpdate event to prevent the user from saving a record that does not meet your validation rules.

 

[Pat Hartman]

You don't need the code in two events. You only need to place the code in the BeforeUpdate event and you don't need to use .SetFocus. If you cancel the update event, the cursor will stay in the field.

Private Sub Jobs_JobNumber_BeforeUpdate(Cancel As Integer)
If DCount("[JobNumber]", "Jobs", "[JobNumber] = '" & [Forms]![frmNewJob]!Jobs_JobNumber & "'") > 0 Then
    MsgBox "That customer number already exists.", vbInformation, "User ID Already Exists"
    Cancel = True
End If
End Sub

 

[CEH]

Thanks!

That works perfect...... And does exactly what I want.