fake saving records

[Hmadyson]

This is a very helpful posting which will help me save and then do stuff to the form.
thread702-61718

I need to compare old and new values to bound text boxes before actually saving, because once I save, the oldvalue property will not be there. How do I fake save (check that the record can save as is, but not save it)? I am worried that someone could be trying to add a record with a primary key that is already in the table, or anything else that may effect my data integrity. Is there an easy solution, I don't really want to open a record set and check the primary keys.

 

[ghubbell]

I have a couple of solutions for you however I must first ask, what are you using for a primary key? Is this key decided by the user? Gord
ghubbell@total.net

 

[Hmadyson]

In some tables it is a string, in some it is an auto number. In some there is a multiple key with a string and autonumber. The table that I care most about uses a string key. I did not want to autonumber the key, because I already have a way to uniquely identify the record and I don't want duplicates based on the key I selected.

 

[ghubbell]

Alright, you don't want to open a recordset. What if I give you one...!
Base your form right off the table. Make the primary key field (I call it KeyField here), the first one users use (so they don't waste time). In its after update event copy/paste this:

On Error GoTo ErrKF
Dim ctl As Control, Response As Integer, SQL1 As String, Rs As Recordset

If IsNull(Me.KeyField) Or Me.KeyField = "" Then
MsgBox CurrentUser() & ", please add an appropriate primary key.", vbInformation, "Key field required."
Set ctl = Me.KeyField
Response = acDataErrContinue
ctl.Undo
ctl.SetFocus
Else
SQL1 = "SELECT YourTableName.* FROM YourTableName" 'check for dup keys
Set Rs = getcurrentdb.OpenRecordset(SQL1, dbOpenDynaset)
If Rs.RecordCount = 0 Then
Rs.Close
Exit Sub
End If
Rs.MoveFirst
Do Until Rs.EOF

If Rs!KeyFieldName = Me.KeyField Then

Rs.Close
MsgBox "There is already a primary key ''" & Me.KeyField & "''. Please chose an alternate.", vbInformation, "Duplicate key..."

Me.KeyField = Empty

Exit Sub
End If
Rs.MoveNext
Loop
Rs.Close
End If

ExitKF:
Exit Sub

ErrKF:
MsgBox Err.Number & " " & Err.Description, vbInformation, "Key ''check'' error."
Resume ExitKF


All you have to do is change the values here for the name of your table, and the name of your field. If you need some help with this or really need an alternative I'll find you one! Let me know,


Gord
ghubbell@total.net

 

[Hmadyson]

Thank you. :-V I was just looking for whatever would be the fastest. If this is what you think will be fastest, I will use it.