Use the same for for data entry and later editing (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 09:54
Joined
Dec 5, 2017
Messages
843
Hi All -

I have read in another post that it is possible to use the same form to do both data entry and also for editing at a later date. OpenForm was mentioned. There was no example given though.

How do I utilize OpenForm to do this and how do I functionally present this to the options to either begin a new record or edit an existing one to a user?

Thanks!

Tim
 

Ranman256

Well-known member
Local time
Today, 09:54
Joined
Apr 9, 2015
Messages
4,339
the form opens the data. you can edit existing record,
or
click NEW RECORD icon on the record counter
or
go to the last record then move to the next record to add new
or
open the form for each:
(will open to view/edit)
docmd.openform "fMyform"

or (to add data)
docmd.openform "fMyform" ,,,,acFormAdd
 

Zydeceltico

Registered User.
Local time
Today, 09:54
Joined
Dec 5, 2017
Messages
843
or
open the form for each:
(will open to view/edit)
docmd.openform "fMyform"

or (to add data)
docmd.openform "fMyform" ,,,,acFormAdd

I am assuming this means that: 1) I have a button for each ("Add New" button and "Edit Existing" button) on some menu form and 2) the form to be used is set "Yes" for Data Entry?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:54
Joined
Jul 9, 2003
Messages
16,245
You are correct, it is considered good practice to use one form for as many tasks as possible.

If you are going to edit records, then you are going to need some method of finding the record you want to edit.

One way is to add a combobox. The combo box wizard can be utilized to create the necessary combobox.

In addressing a different problem, I actually show how to create such a combobox on my blog here:-

http://www.niftyaccess.com/northwind-problem-and-solution/



Sent from my SM-G925F using Tapatalk
 

Zydeceltico

Registered User.
Local time
Today, 09:54
Joined
Dec 5, 2017
Messages
843
You are correct, it is considered good practice to use one form for as many tasks as possible.

Thanks Uncle Giz.

Is that also a "yes" that I set the form globally to Data Entry = "yes" or does "docmd.openform "fMyform" ,,,,acFormAdd" accommodate the data entry functionality without setting the entire form to Data Entry = "yes?"
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:54
Joined
Sep 21, 2011
Messages
14,052
I have approached it by just having one button.

Code:
Private Sub cmdProgress_Click()
DoCmd.OpenForm "frmProgress", , , "CadetIDFK=" & Me.CadetID, , acDialog, Me.CadetID
End Sub

So I filter for the correct key and also pass it in as an OpenArgs

Then in the destination form I have

Code:
Private Sub Form_Current()
If Me.NewRecord Then
    Me.CadetIDFK = Me.OpenArgs
End If
End Sub

So on first entry I get a new record, and after that I get related records.
To add a new record I use the * in the navigation buttons
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:54
Joined
Jul 9, 2003
Messages
16,245
Thanks Uncle Giz.

Is that also a "yes" that I set the form globally to Data Entry = "yes" or does "docmd.openform "fMyform" ,,,,acFormAdd" accommodate the data entry functionality without setting the entire form to Data Entry = "yes?"

First, I'm out of the office so to speak, can't check anything on the PC, so relying on my memory!

I think you could set data entry to true, but I don't, not usually.

It appears that you want to be able to open the form in different conditions, in one condition for data entry and another for searching the records.

If that's the case then I would recommend a menu system, and one of the best I've found is the "Switchboard" the built in one provided by Microsoft Access.

However the latest version is macro driven, something I tend to avoid, (macros)...

The switchboard system provided by Microsoft has got a bit of a bad rep because it's notoriously difficult to set up, if you are not used to it. However, once you get the hang of it is not too bad!

It's a bit like RegX, you either hate it, or you love it!




Sent from my SM-G925F using Tapatalk
 

Wayne

Crazy Canuck
Local time
Today, 09:54
Joined
Nov 4, 2012
Messages
176
I have found (after trying a few other possibilities) that Uncle Gizmo is right - it is best to use the same form for both editing existing and adding new records. I have custom navigation and search buttons along the bottom of the forms to navigate and find existing records, and separate buttons to add new and delete records. It works well for me.

I enter all my service records, and the service dates can be up to 6 months into the future. One dilemna I found was I wanted the form to open on a record with a current service date, and not just to the first or last record. This is the code I use to achieve that and it works well.

Code:
Private Sub btnOpenOrders_Click()

    On Error GoTo Err_btnOpenOrders_Click
    
    Dim rs As Object
            
    DoCmd.OpenForm "frmOrders", acNormal, , , acFormEdit, acWindowNormal
    
    Set rs = Forms!frmOrders.RecordsetClone
    rs.FindFirst "[ServiceDate] = Date()"
    Forms!frmOrders.Bookmark = rs.Bookmark
    
    If rs.NoMatch Then
    rs.FindFirst "[ServiceDate] > Date()"
    Forms!frmOrders.Bookmark = rs.Bookmark
    End If
    
    Set rs = Nothing
    
    DoCmd.Close acForm, "frmMainMenu"
    
Exit_btnOpenOrders_Click:
    Exit Sub
    
Err_btnOpenOrders_Click:
    MsgBox Err.Description, vbInformation
    Resume Exit_btnOpenOrders_Click
    
End Sub
 

Zydeceltico

Registered User.
Local time
Today, 09:54
Joined
Dec 5, 2017
Messages
843
I have found (after trying a few other possibilities) that Uncle Gizmo is right - it is best to use the same form for both editing existing and adding new records.

Thanks! I modified your code and now am using the same form both data entry and viewing/editing existing records. Cool.

Definitely improves consistency and minimizes clutter. Very good!

I'm just tangling with the error handling part of your code. It works great if I "comment" the code relating to error handling. I modified it to match my button's control name ("cmdOpenfrmWeldTestEdit") thinking that would suffice but it obviously doesn't. I'm only vaguely familiar with coding in VBA. I can usually read it but not fluent by any degree - especially with error handling - so I am not entirely sure what I am reading or what I should be looking at to troubleshoot. When I am on the main menu form and click the button named cmdOpenfrmWeldTestEdit I receive the error message: "Compile Error: Sub or Function not defined." Here is the code:

Code:
Private Sub cmdOpenfrmWeldTestEdit_Click()
    On Error GoTo Err_cmdOpenfrmWeldTestEdit_Click
    
    Dim rs As Object
    
    DoCmd.OpenForm "frmWeldTest", acNormal, , , acFormEdit, acWindowNormal
    
    Set rs = Forms!frmWeldTest.RecordsetClone
    rs.FindFirst "[DateTime] = Date()"
    Forms!frmWeldTest.Bookmark = rs.Bookmark
    
    If rs.NoMatch Then
    rs.FindFirst "[DateTime] < Date()"
    Forms!frmWeldTest.Bookmark = rs.Bookmark
    End If
    
    Set rs = Nothing
    
    'DoCmd.Close acForm, "frmWeldTestMenu"
    
Exit_cmdOpenfrmWeldTestEdit_Click:
    Exit Sub
    
Err_cmdOpenfrmWeldTestEdit_Click
    MsgBox Err.Description, vbInformation
    Resume Exit_cmdOpenfrmWeldTestEdit_Click
    
End Sub

I'm also wondering what my options are for the code term "FindFirst." Is there a "FindLast" or "FindMostRecent?" Is there a website somewhere or help file that will tell me all of my options for that command?

Thanks for the help!

Tim
 
Last edited:

Wayne

Crazy Canuck
Local time
Today, 09:54
Joined
Nov 4, 2012
Messages
176
I'm glad it meets your needs. As for .FindFirst, it will loop through the records and simply find the first record that matches the criteria (in this case - today's date). In my code, I wanted it to open at the first record with today's date as the Service Date, and if there were no records with that Service Date, to find the first one after that date.

You left out a colon sign at the end of the line "Err_cmdOpenfrmWeldTestEdit_Click" (see below in red). Also, you modified the .NoMatch code to look for a date before today's date. If your form's records are sorted by that date field, then it will simply go to the first record in the database (if it does not find one matching today's date), and you do not need this code to do that.

Code:
Private Sub cmdOpenfrmWeldTestEdit_Click()

    On Error GoTo Err_cmdOpenfrmWeldTestEdit_Click
    
    Dim rs As Object
    
    DoCmd.OpenForm "frmWeldTest", acNormal, , , acFormEdit, acWindowNormal
    
    Set rs = Forms!frmWeldTest.RecordsetClone
    rs.FindFirst "[DateTime] = Date()"
    Forms!frmWeldTest.Bookmark = rs.Bookmark
    
    If rs.NoMatch Then
    rs.FindFirst "[DateTime] < Date()"
    Forms!frmWeldTest.Bookmark = rs.Bookmark
    End If
    
    Set rs = Nothing
    
    'DoCmd.Close acForm, "frmWeldTestMenu"
    
Exit_cmdOpenfrmWeldTestEdit_Click:
    Exit Sub
    
Err_cmdOpenfrmWeldTestEdit_Click[COLOR="Red"][B]:[/B][/COLOR]
    MsgBox Err.Description, vbInformation
    Resume Exit_cmdOpenfrmWeldTestEdit_Click
    
End Sub
 

Zydeceltico

Registered User.
Local time
Today, 09:54
Joined
Dec 5, 2017
Messages
843
If your form's records are sorted by that date field, then it will simply go to the first record in the database (if it does not find one matching today's date), and you do not need this code to do that.

Ahhhhhh....said the blind man. Very important colon. Thanks. Better now.

Yeah - I would like FindFirst to return the same as your intention.

I changed the < to a > but it is still returning the record with the earliest date instead of the most recent. I wonder if that is because my records aren't "sorted by that date field?" Also, my DateTime field is a General datatype in that it stores both Date and Time in the same field. Would that affect anything?

I should also mention that I have DateTime default value set to =Now() if that matters.

Here's what I have currently. I commented out the line to close the main menu as I would like it to remain open.

Code:
Private Sub cmdOpenfrmWeldTestEdit_Click()
    On Error GoTo Err_cmdOpenfrmWeldTestEdit_Click
    
    Dim rs As Object
    
    DoCmd.OpenForm "frmWeldTest", acNormal, , , acFormEdit, acWindowNormal
    
    Set rs = Forms!frmWeldTest.RecordsetClone
    rs.FindFirst "[DateTime] = Date()"
    Forms!frmWeldTest.Bookmark = rs.Bookmark
    
    If rs.NoMatch Then
    rs.FindFirst "[DateTime] > Date()"
    Forms!frmWeldTest.Bookmark = rs.Bookmark
    End If
    
    Set rs = Nothing
    
    'DoCmd.Close acForm, "frmWeldTestMenu"
    
Exit_cmdOpenfrmWeldTestEdit_Click:
    Exit Sub
    
Err_cmdOpenfrmWeldTestEdit_Click:
    MsgBox Err.Description, vbInformation
    Resume Exit_cmdOpenfrmWeldTestEdit_Click
    
End Sub
 

Wayne

Crazy Canuck
Local time
Today, 09:54
Joined
Nov 4, 2012
Messages
176
You say you have your DateTime field set to a default value of Now() - correct? But that is only for initial data entry. You also said that this is a combined field of Date and time. Then possibly this code might not work. What type of field is it? Is it a Date field, or a general text field?

Wayne
 

Zydeceltico

Registered User.
Local time
Today, 09:54
Joined
Dec 5, 2017
Messages
843
What type of field is it? Is it a Date field, or a general text field?

It's a Date field. It records a value like this 1/24/2019 8:15:00 - and I need the time stamp.

It sounds like I might be better off separating the Time from the Date if I want to be able to use your function - - - which I like quite a lot as 1) it appears to do exactly what I need and 2) I understand it's logic.

I had Date and Time in separate fields once upon a time but was counselled by some with strong opinions that I was making a mistake in doing that.

It appears that I might be going back to having two fields instead of one - which is A-OK with me if it works to getting what I need accomplished.
 

Zydeceltico

Registered User.
Local time
Today, 09:54
Joined
Dec 5, 2017
Messages
843
You are correct, it is considered good practice to use one form for as many tasks as possible.

If you are going to edit records, then you are going to need some method of finding the record you want to edit.

One way is to add a combobox. The combo box wizard can be utilized to create the necessary combobox.

In addressing a different problem, I actually show how to create such a combobox on my blog here:-

http://www.niftyaccess.com/northwind-problem-and-solution/

Gizmo - I just wanted to let you know that the combobox works like a champ for my needs. Thank you for pointing me to that video.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:54
Joined
Jul 9, 2003
Messages
16,245
Gizmo - I just wanted to let you know that the combobox works like a champ for my needs. Thank you for pointing me to that video.

Thank you for taking the trouble to let me know! Much appreciated.

I've got around 300 videos on my YouTube channel, most of which address some issue with MS Access. I thought I'd mention it, as if you subscribe you will be advised of any new videos I post, and I intend to post a lot more! >>> Please click here to subscribe <<<
 

Users who are viewing this thread

Top Bottom