Error in module

[JHB]

Hi, I'm using code to go to the next record and warn that i'm on the last record so it doesn't proceed to a new record.
..

Why not set the form's property "Allow Additions" to No?

 

[Pat Hartman]

and 9 false/blank records (entered by accident before preventing the navigation buttons going past the last record)

The bad records were entered because you did not properly control validation, NOT because you did not control scrolling.

In the BeforeUpdate event of the form, one of the things you might want to consider doing (especially if you don't have required fields set to required in the table) is to ensure that all required fields have a valid value. If they don't, you would cancel the update to PREVENT the recording from being added or updated.

If Me.txtField1 & "" = "" Then
    Msgbox "Field 1 is required.  Please enter a valid value.",vbOKOnly
    Cancel = True
    Me.txtField1.SetFocus
    Exit Sub
End If
If Me.cboField2 & "" = "" Then
    Msgbox "Field 2 is required."vbOKOnly
    Cancel = True
    Me.cboField2.SetFocus
    Exit Sub
End If
....

 

[Dangerous]

Why not set the form's property "Allow Additions" to No?

It's the main data entry and view form. It would mean duplicating the form exactly and use one to view and one to edit and/or enter records. I didn't what that although it's an option to consider in the future.

 

[Gasman]

You could just set the property on form load depending on the user?

Me.AllowAdditions = False
Me.AllowEdits = False

 

[Dangerous]

The bad records were entered because you did not properly control validation, NOT because you did not control scrolling.

In the BeforeUpdate event of the form, one of the things you might want to consider doing (especially if you don't have required fields set to required in the table) is to ensure that all required fields have a valid value. If they don't, you would cancel the update to PREVENT the recording from being added or updated.

If Me.txtField1 & "" = "" Then
    Msgbox "Field 1 is required.  Please enter a valid value.",vbOKOnly
    Cancel = True
    Me.txtField1.SetFocus
    Exit Sub
End If
If Me.cboField2 & "" = "" Then
    Msgbox "Field 2 is required."vbOKOnly
    Cancel = True
    Me.cboField2.SetFocus
    Exit Sub
End If
....

I don't know exactly why the blank entries occurred but I had moved beyond the last valid entry accidentally, perhaps I exited from the form the wrong way.
All I know is that, if for example I had records 1 to 10 entered correctly, moved to the new record by mistake and exited it without adding data, the next new record I did enter would be #12 (#11 blank & not accessible) and the sequential numbering would be lost.

As for validating the data, apart from the auto-numbered key field, there are only 2 fields that need to be in a valid format and NO field is required. Yes I could set one as required (or set one with a temporary default value, to reuse later, of BLANK as I do when I clear a record that is no longer wanted) but as the problem of blank entries happened by accident it is that I'm have blocked and as I (or the user) now has to locate and click the AddRecord button those accidents shouldn't happen.

 

[Dangerous]

You could just set the property on form load depending on the user?

Me.AllowAdditions = False
Me.AllowEdits = False

Anyone who uses it would need to add or edit records.

 

[JHB]

It's the main data entry and view form. It would mean duplicating the form exactly and use one to view and one to edit and/or enter records. ...

No need for an extra form, you can change it by code.
You must already have some criteria, when it is allowed to add a new record, (when you write "It's the main data entry form"), so here would be the right place for Me.AllowAdditions = True.

 

[Dangerous]

No need for an extra form, you can change it by code.
You must already have some criteria, when it is allowed to add a new record, (when you write "It's the main data entry form"), so here would be the right place for Me.AllowAdditions = True.

TBH turning allow edits on and off every time would be a pain, stopping the navigation buttons taking me to a new record at EOF and making exiting the last field in the tab order taking me back to the first field is a better solution for me.

I have maximised the screen area for the main form removing scroll bars, control box, ribbon etc. The only downside is that to apply the FilterByForm filter I have to use the 'Right Click' dropdown and all buttons are disabled between calling FilterByForm and 'ApplyFilter'.
This is ok for now but I may have to look into adding the 'ApplyFilter' to a data field Event.

I'm building this database for two reasons

1: I NEED it as storing the data can be done easier and quicker on paper but editing and finding specific records or all records matching certain criteria would be near impossible.

2: Building databases, spreadsheets etc. is my 'game playing', while others play minecraft, doom grand theft auto etc. I play at programming. As the database is mainly for me to use I want it to work my way. If others don't like my way then so be it, they won't use it.

 

[JHB]

I won't bother you anymore.

 

[isladogs]

As the database is mainly for me to use I want it to work my way. If others don't like my way then so be it, they won't use it.

That's fine, but I have to ask, why bother posting this thread if you are just going to ignore the simple advice given by others including JHB & Pat Hartman?

 

[Dangerous]

I won't bother you anymore.

You're not bothering me, I welcome all suggestions.

 

[Dangerous]

That's fine, but I have to ask, why bother posting this thread if you are just going to ignore the simple advice given by others including JHB & Pat Hartman?

I'm not ignoring it, some of it I've already tried or have thought about but they don't work for me. I'm in other forums or FB groups for some of my other interests and on some it's me that help out with (hopefully the best) advice the most as I have had a lot of training & experience in that work and I know how frustrating it is when people don't attempt to research prior to asking and then go on to ignore the advice but I keep offering it and many have found it useful. That is why I will spend several hours trawling the internet and trying the different things I find before I come on here to ask.

I can't make fields that must have force the user to enter data when they don't have data for that field every time. That applies to every field other than the key/auto-numbered field and I can't disallow edit or new records on the edit/new record form unless I replicate that form to use as a view only form which I don't think is practical which is why I used the on event code I did but that was replicated on more than one form.

The database worked the way I want it to prior to asking in this thread, using the function provided in a module means editing or amending 1, if required, rather then 1 on every form. I'm trying to write better more organised code than the bad programming I was using.