Changing Field of a Previously Saved Record (1 Viewer)

wilderfan

Registered User.
Local time
Today, 08:26
Joined
Mar 3, 2008
Messages
172
The SAVE button on one of my Forms does a validation check before saving the contents of 3 unbound text boxes.

When there is already a record in the table with values identical to the contents of the first 2 text boxes, I use a Msgbox to ask the user if he/she wants to replace the value of the existing 3rd field (already in the table) with the contents of the 3rd unbound text box.

I've been using Dlookup to determine if there is already a record in the table that matches the values of the first 2 text boxes.

But I'm not sure if I should continue to use Dlookup to get the focus on the record that a user might want to change.

Should I be using something like Search For Record? Or something else?
 

vbaInet

AWF VIP
Local time
Today, 15:26
Joined
Jan 22, 2010
Messages
26,374
I've been using Dlookup to determine if there is already a record in the table that matches the values of the first 2 text boxes.
DLookup() is fine but I prefer using a DCount() instead. If a record exists it will not be zero.
 

vbaInet

AWF VIP
Local time
Today, 15:26
Joined
Jan 22, 2010
Messages
26,374
Perhaps I should explain a bit more. If you're using the value returned by the DLookup() to check against the values in the other two textboxes, then it's fine too.
 

wilderfan

Registered User.
Local time
Today, 08:26
Joined
Mar 3, 2008
Messages
172
I'm trying to change 1 field (the nameID field) of the table tblCredits when the user says they want to change the name of the Director.

Maybe I should show you part of the coding of the SAVE button:




' When entering a Director credit, check if the Episode already has a director
If cboSelectFunction = 3 Then
Dim iNameID As Integer
iNameID = 0

iNameID = Nz(DLookup("nameID", "tblCredits", "episodeID = " & cboSelectEpisode _
& " And functionID = " & cboSelectFunction), 0)
If iNameID > 0 Then
Dim strDirector, strReplacement As String
Dim iResponse2 As Integer

strDirector = DLookup("FirstName & ' ' & LastName", "tblNames", "nameID = " & iNameID)
strReplacement = DLookup("FirstName & ' ' & LastName", "tblNames", "nameID = " & cboSelectName)
iResponse2 = MsgBox(strDirector & " is already listed as the Director of this episode." & vbCrLf & vbCrLf & _
"Do you want to replace " & strDirector & " with " & strReplacement & "?", vbYesNo + vbQuestion, _
"QUESTION: Do you want to change Directors?")

If iResponse2 = vbYes Then
'Enter coding to replace existing Director
txtNameID = cboSelectName
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
Call btnClear_Click
Exit Sub
Else
Call btnClear_Click
Exit Sub
End If
End If
End If
 

wilderfan

Registered User.
Local time
Today, 08:26
Joined
Mar 3, 2008
Messages
172
Sorry about the lack of indentation on my coding.

I don't know how to insert the coding the way it looks in VB editor.
 

Users who are viewing this thread

Top Bottom