Avoiding the "can't save the record at this time" pop-up

[cpFAL96]

Hey everyone. I just started learning in Access (came over from Excel a few weeks ago). I'm in the process of developing a database for my firm. It'll include detailed client information, user log-ins, allow users to make billing entries, etc.



I am getting stuck on this one form that I'm building. It is a very simple form that is supposed to update a job description and rate. Basically the user should be able to select the job they want to edit from a combo box, it brings up the description and rate for that job, they click edit, and they can make the change. They should also be able to add new records by clicking an "add new" button.

For the most part it is working, but I'm trying to build in fail-safes for when the users don't work it perfectly. Currently when I click on "add new" and then close the form, I'm getting the error message that "Access can't save the record at this time." Have played with using different events like form_unload or form_beforeclose but it's not working.

This is the all the code I have in this form's module currently -

Private Sub EditJobRefreshBtn_Click() 'refresh/requery manual button
Me.Refresh
Me.JobDescriptionBx.Requery
Me.JobDescriptionBx.SetFocus

'lock fields when moving to new record
Me.EditJobTxt.BackColor = RGB(245, 245, 245)
Me.EditJobTxt.Locked = True
Me.EditHourlyRateTxt.BackColor = RGB(245, 245, 245)
Me.EditHourlyRateTxt.Locked = True

End Sub


Private Sub Form_AfterInsert() 'refresh/requery after new record is added
'(so that job list is updated)

Me.Refresh
Me.JobDescriptionBx.Requery

End Sub


Private Sub Form_Current() 'runs when form is opened or goes to a different record

Me.JobDescriptionBx.SetFocus

'lock fields when moving to new record
Me.EditJobTxt.BackColor = RGB(245, 245, 245)
Me.EditJobTxt.Locked = True
Me.EditHourlyRateTxt.BackColor = RGB(245, 245, 245)
Me.EditHourlyRateTxt.Locked = True

End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer) 'triggered when user changes a value

'if job description is blank and it's a new record, cancel entry (no save)
If Nz(Me.EditJobTxt, "") = "" Then
    If Me.NewRecord Then
        Me.Undo
        Cancel = True
        Exit Sub
    'if job description is blank on a existing record, require description
    Else
        MsgBox "Job description is required. Record will not be saved.", vbExclamation, "Missing information"
        Cancel = True
        Me.EditJobTxt.SetFocus
        Exit Sub
    End If
End If

'If rate is null or blank, make it 0
If IsNull(Me!EditHourlyRateTxt) Or Me!EditHourlyRateTxt = "" Then
    Me.EditHourlyRateTxt = 0
End If

End Sub


Private Sub AddJobBtn_Click() 'adds new record

'go to new record
DoCmd.GoToRecord , , acNewRec

'unlock fields
Me.EditJobTxt.BackColor = RGB(255, 255, 255)
Me.EditJobTxt.Locked = False
Me.EditHourlyRateTxt.BackColor = RGB(255, 255, 255)
Me.EditHourlyRateTxt.Locked = False

Me.EditHourlyRateTxt.Value = ""
Me.EditJobTxt.Value = ""
Me.EditJobTxt.SetFocus

End Sub


Private Sub EditJobBtn_Click() 'enables editing

Me.EditJobTxt.BackColor = RGB(255, 255, 255)
Me.EditJobTxt.Locked = False
Me.EditHourlyRateTxt.BackColor = RGB(255, 255, 255)
Me.EditHourlyRateTxt.Locked = False
Me.EditJobTxt.SetFocus

End Sub


Private Sub JobDescriptionBx_AfterUpdate() 'updates the job description and rate fields based on user's selection in combo box

If Not Me.NewRecord Then

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "JobID = " & Me.JobDescriptionBx
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End If


Me.EditJobTxt.BackColor = RGB(245, 245, 245)
Me.EditJobTxt.Locked = True
Me.EditHourlyRateTxt.BackColor = RGB(245, 245, 245)
Me.EditHourlyRateTxt.Locked = True

End Sub

Can anyone help me figure out what's going wrong? Would really appreciate it, thank you!

[cpFAL96]

Here's a screenshot of what the form looks like visually.

[Welshgasman]

Where is the code for the Save button?
Best to upload a copy of the DB with just enough to see the issue.

[cpFAL96]

Where is the code for the Save button?
Best to upload a copy of the DB with just enough to see the issue.

Sorry, it is the "refresh" button.

[cpFAL96]

Best to upload a copy of the DB with just enough to see the issue.

My file size is too big too upload here apparently, even with just the form and the corresponding table with only 4 records.

[Welshgasman]

My file size is too big too upload here apparently, even with just the form and the corresponding table with only 4 records.

You could try a Compact and then zip, then upload?

[xps35]

I think you're making things far too complicated for yourself. If you want to build a complete database this way, it'll take a while.
You don't need any code at all to edit or enter data. Just link the table you want to edit to the form and the table fields to the form fields, and everything you type into a field on the form will go directly into the table.

[cpFAL96]

I think you're making things far too complicated for yourself. If you want to build a complete database this way, it'll take a while.
You don't need any code at all to edit or enter data. Just link the table you want to edit to the form and the table fields to the form fields, and everything you type into a field on the form will go directly into the table.

If it was just me using the database I would, but I'm trying to make it more user-friendly for everyone else. I'm concerned about accidental edits/additions leading to messy data.

[cpFAL96]

You could try a Compact and then zip, then upload?

I got it compacted enough to upload, it's attached on my original post now!

[Micron]

I agree with the sentiment in post 7. Anyway, when adding a record, you are putting it in edit mode with this

Me.EditHourlyRateTxt.Value = ""
Me.EditJobTxt.Value = ""

I checked the table field settings and based on those, confess that the reason escapes me at the moment (but then I'm multi tasking at the moment so maybe I'm not 100% focused). Anyway, I don't see the need for it. A new record typically doesn't require you to set null values or empty strings in fields. It starts out that way by default. If you remove those lines the problem should go away.

The posted db doesn't give much insight into what the rest of the tables will look like, and your comment about coming over to Access from Excel makes me wonder if you'll set upt the other tables like you would for spreadsheets.

[Welshgasman]

If it was just me using the database I would, but I'm trying to make it more user-friendly for everyone else. I'm concerned about accidental edits/additions leading to messy data.

That can all be handled with form events.

[Welshgasman]

Works fine for me?
I amended computer work to 90, and added Access at 120.

[isladogs]

The error occurs when you try to edit an existing entry in the combobox.
You have set Limit to List = Yes so you can't edit it.
Try changing Limit to List to No. You may need to changing other settings for the combo as well (or re-create it)

[Micron]

The error occurred for me as stated in the OP. Simply open form, click add new then close form. I had to use right click menu to close.

[Welshgasman]

The error occurred for me as stated in the OP. Simply open form, click add new then close form. I had to use right click menu to close.

OK, just doing that, I got that error.
However commenting out

'Me.EditHourlyRateTxt.Value = ""
'Me.EditJobTxt.Value = ""

in the AddJobBtn code removes that error?

No need to set the controls if you have just issued a DoCmd.GoToRecord , , acNewRec with what you have with the tables at present.