SaveRecord isn't available now

[swmagic]

I open a form and prepare to add records:
DoCmd.GoToRecord , , acNewRec

The user adds in fields to the form (like a new SSN). 2 new records are created in 2 existing tables.
Given the SSN, I need to lookup the first and last name from tblEmployee to include them in one of the new records (alas, twas not my database, and yes it is NOT normalized correctly, sigh).

Ideally I thought I could use a DLOOKUP but am getting an error "SaveRecord isn't available now" and I'm pretty sure I've walked over the first DoCmd.GoToRecord,,acNewRec.

Anybody have any other generous thoughts on how I can accomplish this?


Private Sub Form_Open(Cancel As Integer)

DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub cmdSBNbrClos_Click()
Dim strSQL As String
On Error GoTo Err_cmdSBNbrClos_Click

[SuppressPrintFlag] = chkPrint
[Note1] = txtNote1
[Note2] = txtNote2
[Note3] = txtNote3
[Note4] = txtNote4
[Note5] = txtNote5


Dim varX As Variant
varX = DLookup("[Last Name]", "tblEmployee", "[SSN] = '" & Me.[StaffSSN] & "'")
varX = DLookup("[First Name]", "tblEmployee", "[SSN] = '" & Me.[StaffSSN] & "'")
[EntryDate] = Date
[UpdateDate] = Date
DoCmd.RunCommand acCmdSaveRecord

 

[BuilderSpec]

Hi

Firstly you have specified the code in the form open to make the form an "Add record" form. You could do this simply by specifying the Data property on the form to be DataEntry=Yes and then you can remove the form_open code altogether.

The "save" problem might be that the record is already saved and it won't let you save it again. try putting

on error resume next

on the line before the

DoCmd.RunCommand acCmdSaveRecord

Then hopefully it should not fail. You will just have to check if the record has in fact saved.





Hope this helps!

Regards

BuilderSpec

 

[PWise]

Change:

DoCmd.RunCommand acCmdSaveRecord

To:

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord

 

[PHV]

Furthermore, what are you doing with varX ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[swmagic]

Well I've tried all of your options, but I still get the Saverecord error.

Also I thought that varX stores the result of the lookup and that it automatically maintains it in the [Last Name] and [First Name] fields without my having to explicitly load it from varX (but an attempt to load fails.

I really appreciate everyone contributing. Any next ideas?


Option Compare Database

Private Sub Form_Open(Cancel As Integer)
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub cmdSBNbrClos_Click()
Dim strSQL As String
Dim varX As String

varX = DLookup("[Last Name]", "tblEmployee", "[SSN] = '" & Me.[StaffSSN] & "'")
varX = DLookup("[First Name]", "tblEmployee", "[SSN] = '" & Me.[StaffSSN] & "'")
On Error GoTo Err_cmdSBNbrClos_Click

[SuppressPrintFlag] = chkPrint
[Note1] = txtNote1
[Note2] = txtNote2
[Note3] = txtNote3
[Note4] = txtNote4
[Note5] = txtNote5
[Note6] = txtNote6
[Note7] = txtNote7
[Note8] = txtNote8
[Note9] = txtNote9
[Note10] = txtNote10
[EntryDate] = Date
[UpdateDate] = Date
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord

 

[BuilderSpec]

VarX will contain the value of [LastName] after the first call ( or "" if not found I think ! ) but then you do nothing with VarX!

You then call make the second DLookup call which will populate VarX with the First Name or "" if not found but again you do nothing with it ...


????



Hope this helps!

Regards

BuilderSpec

 

[swmagic]

OK, so what I get is that [Last Name] and [First Name] as items in the table are NOT loaded with the lookups like [Note1], [SuppressPrintFlag] = chkPrint are.

so when I do something with them:
varX = DLookup("[Last Name]", "tblEmployee", "[SSN] = '" & Me.[StaffSSN] & "'")
[Last Name] = varX
varX = DLookup("[First Name]", "tblEmployee", "[SSN] = '" & Me.[StaffSSN] & "'")
On Error GoTo Err_cmdSBNbrClos_Click
[First Name] = varX

I get the error:
"Staff Budget can't find the field "|" referred to in your expressions"

I'm confused as Note1, Note2 etc are all in Staff Budget also and they don't have an issue.

???