How to allow edits, but disable additions?

[ItsRoland]

I have buttons on my form that change the data entry property from true to false and vice versa, so the user can go from adding new records to editing them. The issue is that while editing records it is possible to accidentally make a new record instead of change an existing one. To solve this, I tried changing the "AllowAdditions" property to false, but this makes the subforms invisible when you open them. So I tried setting the subforms to go to the first record OnLoad and OnOpen, but neither worked.



I've searched around and haven't been able to find anything on setting a form to allow edits but not additions, only the other way around (aka read only).

Is it even possible to not allow additions but allow editing? and if so, how do you do it? I could be going about it the wrong way.


Any help is appreciated!

[Gicu]

On the current event of the form you can use the NewRecord property to check if you are at a new record, prompt user that they can't add record here then use Me.Undo to go back.

Cheers,
Vlad

[ItsRoland]

I don't think we're on the same page

When the form opens, data entry is defaulted to "no". The only way the user is able to add new records is by clicking the command button that changes data entry to "yes".

Allow additions is also defaulted to "no", which is why when you open the form the subforms are not visible. I want the user to be able to edit records but not add any additions when they open the form. If they click the command button, they'll be able to add new records (This is working properly, if you click the button, the subforms are visible again). Clicking the command button again changes data entry back to "no", and the subforms become invisible again.

The desired result is: when you open the form you can view the records and make edits to them, clicking the command button allows the user to create new records, and clicking it again changes it back to editing only.

The issue I'm trying to overcome here is that if allow additions is set to "yes" and data entry is set to "no", it is possible to accidentally add a new record without meaning to. I was trying to overcome this by setting allow additions to "no", but this makes the subforms invisible, which is supposedly because they're not opening to the first record.


EDIT:

Okay so what you were telling me clicked and I realized my mistake, I apologize again for that.

I did what you said, and it caused issues with the form. It would work at first (at least I think it would, hadn't gotten around to testing it it stopped new entries), but after using the command button to go from data entry "yes" to "no" a few times, it starts to give errors in my code. Heres what I did:

 If Me.DataEntry = False Then

    If Me.NewRecord = True Then


        Me.Undo
    
    End If
End If

I used the first if statement because if data entry is set to "yes" then I don't want to prevent new records. I think the second if statement is whats causing the issues, because every time I change data entry from "yes" to "no" it tries to undo the new record, which then causes the code in the button that changes the data entry value to give an error, but thats just my theory as to why this is happening.

If you know a better way to code this please let me know, I'm not sure how to set this up so when it tries to add the record it will stop it.


I hope that clears things up, unless I'm the one not understanding what you're trying to tell me, in which case I apologize.

In either case, I appreciate the response.

[Gicu]

because every time I change data entry from "yes" to "no" it tries to undo the new record, which then causes the code in the button that changes the data entry value to give an error

I think you just need to save the new record before changing the data entry from yes to no: Me.Dirty=False

Cheers,
Vlad

[isladogs]

You probably know most of the following but hopefully something will help

Data Entry = No is the default setting - it allows you to see all existing records but (unless otherwise blocked) you can also add new records
Data Entry = Yes - opens a form to a new record - no other records can be viewed

Allow Additions - Yes to allow new records - No to prevent
Allow Edits - Yes to allow editing - No to prevent
Allow Deletions - same idea

Record Locks - determines whether & how records in the underlying table/query are locked - default = no locks

You will see an 'interesting' result if you set Data Entry = Yes & Allow Additions = No

HTH

[ItsRoland]

After adding Me.Dirty=False, I'm still getting errors in my vba code saying "couldn't locate table" and things like that on statements that set record sources to tables.

Is there any other way of setting the form/subforms to 'Data Entry = False' and 'Allow Additions = False' without the subforms disappearing?

Its very strange, because these settings do exactly what I want them to do, but since the subforms just disappear when 'Allow Additions = False' it makes this functionality of access pointless, which makes me feel like there's some other problem going on here.

[Gicu]

Could you upload a sample db with the form in question (you can leave the tables blank or add a couple dummy records) so we could have a look?

Cheers,
Vlad

[ItsRoland]

The file manager to attach files to posts doesn't do anything when I try to upload my copy of the database I'm working with. I've gone in and deleted as much as I could without completely breaking the form in question and its still too large at just over 12.5KB. At least I assume its too large considering when I try to upload it, nothing happens.

If its not one thing its another



I'll try to upload a copy of it tomorrow morning, The workday is about to be over here and I don't have access to the database from anywhere but my work machine.

[Gicu]

Try to zip it first.

[ItsRoland]

Cyber Risk Database - Working - Copy.zip


Good idea, idk how I didn't think to do that.

There's a lot of stuff that probably isn't going to function properly since I erased most of the data in the tables. Just let me know if you can get Allow Additions = False without the subforms disappearing/ find a way to prevent adding new records when Data Entry = False. The code we discussed is no longer in there, had to take it out while I continued to try to troubleshoot since it was returning errors, maybe I was doing something wrong.

Side note; There's probably stuff in there that isn't set up optimally, I've had to learn access and VBA on the job. Just know that I am aware of it, and will be fixing it later down the line, the goal atm is just to get it functioning asap.


Thanks again for your help, I really appreciate it.

[Gicu]

What form is the one in question?

[Gicu]

found it fAssets (Entry/Lookup).

[Gicu]

Have a look now. If DataEntry= False and NewRecord=True it prompts you that you cannot add record in this mode then requeries the form (goes back to first record). I have also changed a line of code (search for Vlad in comments in the form module) as it was looking for a subform that was not there and the code wouldn't compile.

I also noticed that you have table lookup fields (asset type, asset category, etc.), I strongly recommend you remove the lookups from the table and use combo boxes in the appropriate forms, reports. Read here some more about this:http://access.mvps.org/access/lookupfields.htm

Cheers,
Vlad

[ItsRoland]

I'm not entirely sure what you've done, other than fix the form name in the code. Am I missing something?

You mention setting "DataEntry=False and NewRecord=True", but I don't see a property called "NewRecord" anywhere, and the form is still behaving the exact same, as in its still adding records to the table. I haven't been able to reproduce the prompt you mentioned either.

Could you please explain how you got that prompt, as well as what you did to get the form to stop adding new records?

Sorry if I'm missing something obvious here

[Gicu]

When you open the form you are in Edit mode (DataEntry =False). Click on the record navigation on the bottom to go to a new record and you should get the prompt. Using you toggle button to switch between DataEntry=False and DataEntry=True seems to work without any errors. Wasn't that what you wanted?

Cheers,
Vlad