Edit Existing Records by Using a Combobox to Automatically Fill Out Fields within the Form

[CAhawkeye10]

I've just begun using Microsoft Access (2013) and I've set up a table and a form to input records into a table. I however would also like the ability to edit an existing record that was previously entered through the form. Ideally, I would like to set up the same form used to create new records to also select existing records so that it populates the rest of the fields within the form so that the user can edit that existing record with the previously entered information.Like I said, I'm just a day into this so I have little to no experience with Access or its features, any help would be greatly appreciated.Thanks.

[June7]

The actions you describe have been topics of numerous threads. Search forum.

Also, for retrieving existing record, review:

http://www.allenbrowne.com/ser-62.html
http://www.datapigtechnologies.com/f...tomfilter.html

For copying existing data to a new record, that definitely requires more VBA code. More than one way to accomplish - just depends on situation.

[CAhawkeye10]

Thank you for pointing me in the right direction! The video is exactly what I needed!I'm sure I'll be back with more questions soon.

[CAhawkeye10]

I have created (2) forms, one for the user to input new data and one for the user to change existing data (using the video you provided above). I've made it so that an error message pops up if the user doesn't fill in a required field on the new data form. However, I cannot figure out how to have the same error message pop up if required fields are deleted on the existing data form. I'm assuming this has something to do with the Me.Requery code I've incorporated to get all of the fields to populate with the existing data. I've included the code below, any help is greatly appreciated. Thanks!

Private Sub myselector_AfterUpdate()
Me.Requery
End Sub

Private Sub SubmitAndClose_DblClick(Cancel As Integer)
On Error GoTo Err_SubmitAndClose_DblClick
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
Exit_SubmitAndClose_DblClick:
Exit Sub
Err_SubmitAndClose_DblClick:
MsgBox Err.Description
Resume Exit_SubmitAndClose_DblClick 
End Sub

Private Sub SubmitAndOpen_DblClick(Cancel As Integer)
On Error GoTo Err_SubmitAndOpen_DblClick 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
DoCmd.OpenForm "Existing Change Estimate" 
Exit_SubmitAndOpen_DblClick:
Exit Sub 
Err_SubmitAndOpen_DblClick:
MsgBox Err.DescriptionResume Exit_SubmitAndOpen_DblClick 
End Sub

Private Sub Cancel_DblClick(Cancel As Integer)
DoCmd.Close
End Sub

[June7]

I would make one form serve both purposes. I prefer the Allen Browne VBA approach.

Options for data validation:

1. set the fields as required in the table and let Access nag users if fields are not populated
2. Validation Rule property of controls
3. VBA code

[CAhawkeye10]

Either way, I can't figure out why an error message is not popping up when information is deleted from a required field. It is allowing the form to be submitted without that required information.

[June7]

Are you using option 1?

I don't see any code that checks values of controls. In button event would be like:

If IsNull(Me.controlname) Then
MsgBox "Enter data into control. Exit canceled."
Else
DoCmd.Close
End If

Record is committed to table when form or table is closed, move to another record, run code. Why are you using code to save record?

[CAhawkeye10]

Sorry, I'm a total n00b with access as I've just started. I tried entering the code as you provided but now I just get the message "Enter data into control. Exit cancelled." every time, even if all the fields are filled out. Here's what I have typed in now:

Private Sub myselector_AfterUpdate()
Me.Requery
End Sub

Private Sub SubmitAndClose_DblClick(Cancel As Integer)
If IsNull(Me.[CAN Notes]) Then
MsgBox "Enter data into control. Exit canceled."
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
End If 
End Sub

Private Sub SubmitAndOpen_DblClick(Cancel As Integer)
If IsNull(Me.[CAN Notes]) Then
MsgBox "Enter data into control. Exit canceled."
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.CloseD
oCmd.OpenForm "Existing Change Estimate"
End If 
End Sub

Private Sub Cancel_DblClick(Cancel As Integer)
If IsNull(Me.[CAN Notes]) Then
MsgBox "Enter data into control. Exit canceled."
Else
DoCmd.CloseEnd If
End Sub

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[CAhawkeye10]

Hi, please see the below attachment of my example. I want/need to have two different forms (one that creates a record and one that edits an existing record). I've had no issue with creating new records and ensuring that required fields are filled in, right now an error message pops up saying what fields need to be filled in.

I have however had issues when editing existing records. If an existing record is pulled up and I delete a required field, it allows it to be saved like that rather than giving an error message saying what needs to be filled in.

Thanks!

Example.zip

[June7]

I would make one form serve both purposes.

Not seeing suggested code behind either form.

[CAhawkeye10]

Sorry, I may have given you an old version. The attached updated one includes the suggested code. And I understand that one form would be better but I'm doing this for work and it's what management wants and they want two separate ones.

ExampleRev1.zip

Thanks for your time.

[June7]

But management doesn't have to do database maintenance. Managing identical forms for design edits is a nuisance. Why would they care as long as they get the required performance?

Clicking Submit gets error "The command or action 'Save Record' isn't available now." This command is unnecessary. Record is committed to table when form closes, move to another record, or run code.

Rename Comments textbox to tbxComments then change code:

If IsNull(Me.tbxComments) Then

For some reason the Comments textbox has empty string even though the field is null. Don't recall ever encountering this. Try this code instead:

If Me.tbxComments & "" = "" Then

[CAhawkeye10]

The second one appears to have worked! Thanks!

Now, I have multiple fields that are required, is there a short-hand way to "tag" multiple fields so that I only need one line of code?

[June7]

Several methods

1. Give the controls similar names like: tbxData1, tbxData2, etc.
For i = 1 to {the quantity of controls involved}
If Me.Controls("tbxData" & i) & "" = "" Then
...
Next

2. Set the Tag property of controls with some text value, like: Validate
Then code can cycle through ALL controls on the form and look for that text. http://stackoverflow.com/questions/3...n-a-subform-ac