Create new record problem (1 Viewer)

[John Sh]

I have a single form, bound to a table, to which I can add a new record.
Once all the required fields have been completed the record is automatically saved.
I have tried saving by me.dirty = false and DoCmd.RunCommand acCmdSaveRecord.
Checking if the record is still flagged as new results in negative.
If I now click the new record button I get error 2105,"you can't go to the specified record.
If I move off the current record and back I can then create a new record.
I have also tried me.requery but that takes me back to the first record.
While going off the current record and back works, it is messy.
What am I missing?

 

[theDBguy]

Can you show us your code please?

 

[Mike Krailo]

If I now click the new record button I get error 2105,"you can't go to the specified record.

That's the sort of error you get when AllowAdditions = False. Makes sure it is set to true in your code for the New button.

Private Sub NewBtn_Click()
   Me.AllowAdditions = True
   DoCmd.GoToRecord record:=acNewRec
End Sub

 

[John Sh]

Can you show us your code please?

This is the save code. It is automatically applied once all required fields are completed.
Most of the code is window dressing.

When the error message appears, I click "End" then "New" and I get the new record ready for action.

Private Sub btnSaveRecord_Click()
    Me.Label = True
    isCopy = False
'    Me.Dirty = False
    DoCmd.RunCommand acCmdSaveRecord
    Me.Label = True
    Call istagged
    Me.btnSaveRecord.Caption = "Saved"
    Me.btnSaveRecord.BackColor = RGB(0, 255, 0)
    Me.NewBtn.Enabled = True
    Me.btnCopy.Enabled = True
    Me.CloseBtn.Enabled = True
    Me.btnfirst.Enabled = True
    Me.btnPrevious.Enabled = True
    Me.btnSaveRecord.Enabled = True
'    DoCmd.GoToRecord , , acLast
    Call setLocked([Forms]![main collection], "Z")
End Sub

And the new code. Again much window dressing.

Private Sub NewBtn_click()
    Dim str As String
    If intLevel = 3 Then
        MsgBox "Students are not authorised to create new records!"
        Exit Sub
    End If
    Me.AllowAdditions = True   ( I added this today. It made no difference.)
    DoCmd.GoToRecord , , acNewRec
    Me.NewBtn.Enabled = False
    Me.CloseBtn.Enabled = False
    Me.btnCopy.Enabled = False
    Me.btnSaveRecord.Enabled = False
    Me.lblOrder.Caption = "Order"
    Me.txtAccNo = DMax("AccessionNumber", "main") + 1
    Me.TxtCreateD = Date
    Me.TxtCreatedby = strFullName
    Me.TxtHerbarium = "The Don McNair Herbarium"
    Me.TxtCreateInst = "University of Newcastle"
    Me.cboInAus = "Aust"
    Me.TxtLatDir = "S"
    Me.TxtLongDir = "E"
    Me.cboBot = "Aust"
    Me.cboKOC = "Sheet"
    Me.txtConStat = "S"
    Me.txtStatus = "R"
    Me.txtAccStat = "C"
    Me.txtNameS = "A"
    Me.cboDet = "D"
    Me.Label = True
    Me.Label.BackColor = RGB(218, 255, 94)
    Call setLocked([Forms]![main collection], "Z")
    Me.txtAccNo.Locked = False
    Me.txtAccNo.SetFocus
End Sub

 

[Eugene-LS]

And the new code. Again much window dressing.

Can you post a copy of your application with just the form and required tables/queries.
Just a few (fictitious) records to understand.

 

[John Sh]

Can you post a copy of your application with just the form and required tables/queries.
Just a few (fictitious) records to understand.

The data table is an actual data sample.
The pink controls are required with some being filled when the form opens.
Once the "Shelf No" control is passed, not required info, the "Save" button is called.
If you enter rubbish into the "Epithet" control the system will crash as the "on notinlist" event code is not included.
You can take anything from the dropdowns without breaking the code.
The "Box, Bay and Shelf" controls can be left as "0"

NOTE>>>>This is a ".7Z" file renamed to ".Zip" so it would upload.

 

[Mike Krailo]

I did not get any error when trying your New button. Works for me. Update: I did not enter the required data, my bad.
Yet another Edit:
The reason why the record appears not to save is because you are immediately causing the record to be dirty again with that Me.Label = True line of code. Move that above the command that saves the record and you should be good.

For that other error message, that will happen if the VBE application has the focus and not the Main Collection form. You can force the focus back to the form right above your save command with DoCmd.SelectObject acForm, "Main Collection" to avoid that issue while debugging.

On another note. Your whole database is un normalized and seems to have a lot more problems than just this one. You have a huge amount of public variables for some reason. That usually isn't necessary.

The flow of the controls is kind of weird since the Supra Family and Order fields are above the Family combo. I would put that combo right under the Accession Number and in the after update skip over the two fields that automatically get filled out from the combo.

I find if very annoying that you cannot go back and change your selection for Family after selecting an initial value. That's just a bad UI. I know there are interactions in the values that make it complicated, but you have to figure out how to uncomplicate it for the users. Learn how to use Before Update event to validate all the user data entered before allowing it to be saved.

Hope that helps

 

[Eugene-LS]

Table: "Main" - Total 95 fields
Required fields:
001: AccessionNumber
002: Family
003: Genus

Fields with ValidationRule:
001 : Family - ValidationRule: Is Not Null
002 : Genus - ValidationRule: Is Not Null
003 : Suprafamily - ValidationRule: Is Not Null

 

[Eugene-LS]

Once the "Shelf No" control is passed, not required info, the "Save" button is called.

As far as I can see, you have a lot of small and unhandled errors in your code - if one occurs, the form stops working properly
New Record button works fine until the first error
And I think you're missing an undo button

 

[Mike Krailo]

Yes, this one needs a lot of work Eugene. I would start over and get the tables normalized and go from there.

 

[John Sh]

I did not get any error when trying your New button. Works for me. Update: I did not enter the required data, my bad.
Yet another Edit:
The reason why the record appears not to save is because you are immediately causing the record to be dirty again with that Me.Label = True line of code. Move that above the command that saves the record and you should be good.

For that other error message, that will happen if the VBE application has the focus and not the Main Collection form. You can force the focus back to the form right above your save command with DoCmd.SelectObject acForm, "Main Collection" to avoid that issue while debugging.

On another note. Your whole database is un normalized and seems to have a lot more problems than just this one. You have a huge amount of public variables for some reason. That usually isn't necessary.

The flow of the controls is kind of weird since the Supra Family and Order fields are above the Family combo. I would put that combo right under the Accession Number and in the after update skip over the two fields that automatically get filled out from the combo.

I find if very annoying that you cannot go back and change your selection for Family after selecting an initial value. That's just a bad UI. I know there are interactions in the values that make it complicated, but you have to figure out how to uncomplicate it for the users. Learn how to use Before Update event to validate all the user data entered before allowing it to be saved.

Hope that helps

Thanks Mike. I think setting the label is the answer.
The order of entry was initially set to comply with a paper form with the data in that order. It was easier to leave it that way.
Normalising the thing means multiple forms and this is not conducive to the way the office works. It is much easier for the users to work on a single form. I would love to normalise this setup but.
What you see here is just a small part of a much larger systems so some things may not be quite as they seem.
On top of that, learning to code at 82 is not easy so a few mistakes are allowed as long as the job gets done.
John

 

[Mike Krailo]

The order of entry was initially set to comply with a paper form with the data in that order. It was easier to leave it that way.

But the initial order on your paper form was wrong to begin with. Selecting from that combo box should come first and it doesn't have to look like a paper form. When you make a report on the other hand, you can make it look exactly like the paper form if you wish. As long as the person entering the data doesn't mind that user interface, then it's not a huge deal.

Normalising the thing means multiple forms and this is not conducive to the way the office works.

It's hard to break out of the spreadsheet mindset, but once you do, then the real power of Access is getting rid of the redundancy errors that plague Excel spreadsheets by disallowing bad data to enter into your system. Then you can actually query your data and make meaningful reports. It still amazes me how powerful the queries get on a normalized system.

What you see here is just a small part of a much larger systems so some things may not be quite as they seem.
On top of that, learning to code at 82 is not easy so a few mistakes are allowed as long as the job gets done.

If this is just part of a larger system, then hopefully it is at least working for you to get the job done as you say. Are you sure your not dyslexic and only 28? I'm glad we could help you out with your immediate issue. Live life to it's fullest my friend.

 

[John Sh]

Are you sure your not dyslexic and only 28? I'm glad we could help you out with your immediate issue. Live life to it's fullest my friend.

Hi Mike.
No, I'm not dixlecis. I appreciate your comments. I moved the me.dirty to the bottom of the save code and all is good.
As for the rest. The herbarium collects data on plant species and it is best viewed in it's entirety, reports are seldom if ever needed and the data entry off the hand written sheets is just more convenient if the form copies the sheet.
Initially the data table was the only entry method and there were no controls on what was entered. It has taken me some two years to correct most of the errors and create a system where the critical entries are limited to the various combo lists. There is also a supervisor review of all new entries and any corrections can be made at that time..
Many items such as flower colour are just too variable to control so what goes in is what it is.
By and large all sections of the new system are working as expected and the occasional error is generally fixed there and then.
John

 

[Mike Krailo]

I see, so the data is simply used to search and view on screen. As long as your happy with it John, then life is good.