UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Continuous Form - Allow Edit Single Record, Access 2013    
 
   
helmerr
post Feb 14 2018, 04:43 PM
Post#1



Posts: 45
Joined: 23-August 14



Ok, I've been messing with this for days now so I've decided to ask for help again.

I have a continuous form with bound text box controls which are based off a query. On the top, form a user enters in data and clicks save. In turn, the saved data is displayed in a continuous form below.

Like most people, I'd like to allow a user to edit one line (or at least the previously entered record) in case they made a mistake. At some point, i even found a function (which I have since lost) that toggles through enabling/disabling the lower half of the form but still enables data entry on the top form. I've also been able to use a command button for such purposes, but I cannot get the command button to "toggle back." Another solution I've found was to add another table field for the lock/unlock values... My current solution is to have two separate controls; one to enable and one to disable the form edit function. While this works, it wont allow me to update date in my top form. This code is shown below.

From what I read now, there may be a better solution using conditional formatting, but I cant figure out where to start. (Reference this link.
If not, could someone assist in allowing my top form to add table entries with this code? Thanks

Unlock code:
CODE
Private Sub cmdEdit_Click()

' Used in conjunction with cmdSave_Click()
On Error GoTo Err_cmdEdit_Click
     Me.AllowEdits = True '(sets the record to be editable, regardless if the form has the property set or not)
     Me.cmdSave.Enabled = True  '(enables the Save Record button)
     Me.txtSubLocked = "Unlocked" '(Displays a box on the form that shows it is in "Edit Mode")
     Me.txtSubLocked = True  '(Displays the Unlocked Text)
Exit_cmdEdit_Click:  '(Exit Sub-routine)
    Exit Sub

Err_cmdEdit_Click:  '(Error Trapping)
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_cmdEdit_Click
    
End Sub


Save Code:
CODE
Private Sub cmdSave_Click()

' Used in conjunction with cmdEdit_Click()
On Error GoTo Err_SaveRecord_Click
If Me.AllowEdits = True Then  '(Test if the form allows Edits,  if so)
     DoCmd.RunCommand acCmdSaveRecord  '(Save the changes)
End If
     Me.txtSubLocked.Visible = False '(Turn Unlocked Text off)
     Me.cmdEdit.Enabled = True  '(Enable the Edit Mode button)
     Me.AllowEdits = False  '(Change the form to NOT allow Edits)
Exit_SaveRecord_Click: '(Exit Routine)
    Exit Sub

Err_SaveRecord_Click: '(Error Trap)
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_SaveRecord_Click

End Sub


Top form code:
CODE
Private Sub cmdAdd_Click()
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from qryMileage")

'test required fields
If IsNull(Me.cboVehicleNum.Value) _
        Or IsNull(Me.txtODate) _
        Or IsNull(Me.txtOdometer) Then
    
    MsgBox "Please fill in all required fields."
Else

'add records to able
rec.AddNew
rec("VehicleNum") = Me.cboVehicleNum
rec("ODate") = Me.txtODate
rec("Fuel") = Me.txtFuel
rec("Odometer") = Me.txtOdometer

rec.Update

Set rec = Nothing
Set db = Nothing

MsgBox "Record saved."

'Requery form
Me.Requery

' Clear All Controls
    Me.txtODate = Date
    Me.cboVehicleNum.Value = Null
    Me.txtOdometer = Null
    Me.txtFuel = Null

End If

End Sub
Go to the top of the page
 
GroverParkGeorge
post Feb 14 2018, 06:31 PM
Post#2


UA Admin
Posts: 32,556
Joined: 20-June 02
From: Newcastle, WA


This sounds more like a split form than a continuous form. Is it?

If the form is bound, why do you ALSO use VBA to add new records? What's the reasoning behind that?


--------------------
Go to the top of the page
 
helmerr
post Feb 14 2018, 08:59 PM
Post#3



Posts: 45
Joined: 23-August 14



Hi Grover - thanks for chiming in.
Here are some screenshots; hopefully they help clear up any confusion?

Attached File(s)
Attached File  formDesign.png ( 461.58K )Number of downloads: 7
Attached File  formOpen.png ( 173.72K )Number of downloads: 6
 
Go to the top of the page
 
projecttoday
post Feb 15 2018, 02:39 AM
Post#4


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


You might want to consider a different approach to avoid continued banging your head into a wall. I did one this way and it worked well. Unfortunately, I do not have the database now. What I suggest is that you do away with the top form completely and replace it with a single-record popup form. Your continuous form is placed as a subform on an unbound main form. You also develop a single record form for the same table. When the user clicks on a line on the continuous form you open the single record form as a popup filtered to the selected record. You just need to capture the record id off the continuous form. The user makes the changes and then closed the form. If the changes have to be displayed on the continuous form then you have to requery it. If the user wants to add a new record there is a button for that on the main form which opens the single record form on a new record. When he's done, he closes it.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 15 2018, 11:32 AM
Post#5



Posts: 45
Joined: 23-August 14



Thanks - I think I understand. I'll give this a shot and report back if I have issues.
Go to the top of the page
 
projecttoday
post Feb 15 2018, 11:43 AM
Post#6


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


I just thought it might be simpler if you just use the continuous form by itself without the main form to put it on. You could put the add new record button in the header section.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 15 2018, 12:23 PM
Post#7



Posts: 45
Joined: 23-August 14



How about the following:

If I make a new main form where the user can add NEW records in the form header, then use detail section only from my continuous form as a sub form in the main form...
Then say the user clicks the cmdEdit button on any record in the continuous form, this selected record moves to the top (main form) for edits? I'm just trying to think of a way without adding popups. All other forms are contained in a navigation form.
Go to the top of the page
 
projecttoday
post Feb 15 2018, 01:01 PM
Post#8


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


How is that different from your current form? How about this: just 2 forms, the continuous form for a listing and a single-record update form that resides in the header section of the continuous form?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 15 2018, 05:45 PM
Post#9



Posts: 45
Joined: 23-August 14



That’s it - that’s actually what I was tying to describe. Originally, I was trying to allow edits in the continuous form, from the continuous form, in its original place.

So given what you have just described, would it also be possible to add new records from the top form?

Edit* two forms total? So the continuous form is the main form? Earlier, I was trying to figure out how to make whatever is selected in the continuous form to appear on the top form but so far unsuccessful.
This post has been edited by helmerr: Feb 15 2018, 05:48 PM
Go to the top of the page
 
projecttoday
post Feb 15 2018, 07:01 PM
Post#10


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


Yes. The continuous form is the main form. You can enter new records on the subform which is in the header.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 15 2018, 11:11 PM
Post#11



Posts: 45
Joined: 23-August 14



ok, I'm lost. So I've built a new form using my continuous form "frmLocationMXMain" as the main form (in the detail section). In the header of this form, Ive put "frmLocationMXSub" as a subform that includes a text field and combobox to match the continuous form. There is also an edit and a save button on the continuous form.

It actually looks the exact same as my original setup. However, I still cannot figure out how edit any portion of the continuous form without enabling every record. I was thinking I would be able to click a command button and bring the selected record from the continuous form "frmLocationMXMain" to the "frmLocatinoMXSub" sub form for editing, rather than a popup. How can I make this happen or is it possible; Am I missing something? Any other alternatives than a popup? Thanks
Go to the top of the page
 
projecttoday
post Feb 16 2018, 12:15 AM
Post#12


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


I should have known that you can't put a subform on a continuous form. But we can still do 2 forms on one. Attached is a sample. Open MainForm. You can click over on the left of the list on the lower part of the screen and the form at the top navigates to your record. This is really pretty simple so I hope I got what you're aiming at.

You can do the same thing with a datasheet or a listbox. If you used a listbox you don't need 3 forms.
Attached File(s)
Attached File  ContinuousUpdate.zip ( 26.5K )Number of downloads: 10
 

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 16 2018, 08:34 PM
Post#13



Posts: 45
Joined: 23-August 14



Ok, we're halfway there. I can configure your DB to NOT allow edits within the continuous subform and only ALLOW them at the top subform, which is good. However, it won't allow me to do both edits (in this way) as well as add new records when enabling data entry on the SingleRecordSubForm. Perhaps this is a limitation of access? Is there any VBA code to get around this?
Go to the top of the page
 
projecttoday
post Feb 16 2018, 09:08 PM
Post#14


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


I'm not following. To add a new record you just click on >* on the navigation at the bottom of the form.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 16 2018, 10:06 PM
Post#15



Posts: 45
Joined: 23-August 14



Ah. Our users normally add items in the top form. This is actually the case for 2 of our other continuous forms. The ones I'm working on here are an exception; we just want them to be able to edit in case of a mistake. (Without being able to edit them all at the same time). Meaning If possible, I'd like to only unlock one record at a time for edit. The example you gave does this perfectly, if I just disallow edits in the bottom continuous form.

That being said; is there someway to keep this setup but allow new record entry in the top form? (I know I'm making it harder; I may have to resort to a popup form). frown.gif
Go to the top of the page
 
projecttoday
post Feb 16 2018, 10:29 PM
Post#16


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


Enter new records? Didn't we just discuss that?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 16 2018, 10:50 PM
Post#17



Posts: 45
Joined: 23-August 14



In the database you sent, I can edit entries on the top or bottom part of the form. (I want to be able to edit them in the TOP form; not the bottom. The bottom continuous form allows me to update all records. I do not want to be able to edit in the BOTTOM form due to this). So, I've disallowed edits in the bottom continuous form. This resolves my concern with editing the continuous form, since I can now edit them only in the TOP.

Now, I also want to the user to add entries to the TOP of the form (with custom buttons, for visual purposes); not the bottom form using the Access default buttons.

I'm just asking, with this setup, is there any way to get the user to enter NEW records in the top form, instead of the bottom form. I'm attaching a pic of another continuous form we have for reference.
This post has been edited by helmerr: Feb 16 2018, 10:51 PM
Attached File(s)
Attached File  Messages_Image_285534000__png.png ( 334.96K )Number of downloads: 3
 
Go to the top of the page
 
projecttoday
post Feb 16 2018, 11:22 PM
Post#18


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


Put a button in the header section with this code:

CODE
DoCmd.GoToRecord acActiveDataObject, , acNewRec

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
helmerr
post Feb 17 2018, 06:53 PM
Post#19



Posts: 45
Joined: 23-August 14



You are sooo my hero! Thanks so much for sticking with me on this; I now the form working exactly as I wanted, but better.
For your reference, I'm attaching a screenshot and code of the final product. Now, I'll just repeat the same for my other like forms.

Finished product with 3 forms: frmLocationMXMain, and it's two subforms: frmLocationMXHeader and frmLocationMXDetail

frmLocationMXHeader
CODE
Option Compare Database

Private Sub cmdDelete_Click()

   On Error Resume Next
    RunCommand acCmdDeleteRecord
    
    'Requery forms
    Me.Requery
    Me.Parent.frmLocationMXDetail.Form.Requery
    
    ' Prepare form for new record input
    DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub cmdNew_Click()

    ' Prepare form for new record input
    DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub cmdReset_Click()

    ' Prepare form for new record input
    DoCmd.GoToRecord , , acNewRec
    
End Sub

Private Sub cmdSave_Click()

'test required fields
If IsNull(Me.txtLocation.Value) _
Or IsNull(Me.cboLocationStatus.Value) Then
    MsgBox "Please fill in all required fields."
Else

    MsgBox "Record saved."

    'Requery forms
    Me.Requery
    Me.Parent.frmLocationMXDetail.Form.Requery

    ' Prepare form for new record input
    DoCmd.GoToRecord , , acNewRec

End If

End Sub

Private Sub Form_Load()

    ' Prepare form for new record input
    DoCmd.GoToRecord , , acNewRec

End Sub


frmLocationMXDetail
CODE
Option Compare Database
Option Explicit

Private Sub txtSubLocation_Click()

Dim rs As Object
Set rs = Me.Parent.frmLocationMXHeader.Form.RecordsetClone
rs.FindFirst "[LocationID] = " & Me.txtSubLocationID.Value
If Not rs.NoMatch Then Me.Parent.frmLocationMXHeader.Form.Bookmark = rs.Bookmark

End Sub

Attached File(s)
Attached File  final.png ( 222.2K )Number of downloads: 3
 
Go to the top of the page
 
missinglinq
post Feb 17 2018, 09:05 PM
Post#20



Posts: 4,472
Joined: 11-November 02



QUOTE (projecttoday)
...I should have known that you can't put a subform on a continuous form...

Not really germane to this already solved problem, but FYI, you can place a Subform on a Continuous Form. You just need to fool the Access Gnomes in order to do so! The workaround for having a Subform when the Main Form is in Continuous View requires that you put the Subform either in the Header or the Footer of your Main Form.

You need to:
  • Create your Main Form in Single Form View
  • Place the Subform in Header or Footer Section, whichever you like
  • Save the Form
  • Now go into Properties and change the Form's Default to Continuous View
Linq ;0)>



--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    21st May 2018 - 12:21 AM