Open for to Edit or Add Record

[Robin]

Hello,

I'm haveing a problem getting this to work. I have a Main form on which
there is a listbox populated with a client list and a New Client (bMain1)
button. If I Double Click on a client in the listbox, I want the Client form
to open to that record. If I Click on the New Client (bMain1) button I want
the same form to open to a new record.
Is there some "standard" way to open a form and let the form know from where
it was called? I've already used the OpenArgs to pass a UserID from form to
form.

I've tried the following:

On the Main form:
Private Sub bMain1_Click()
If Me.bMain1.Caption = "New Client" Then
stDocName = "fmClient"
DoCmd.OpenForm stDocName, , , , acFormAdd, , stUserID
ElseIf Me.bMain1.Caption = "New Engagement" Then
etc...
AND
Private Sub lstCMainC_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim stUserID As String
stDocName = "fmClient"
stUserID = Me.vUserID
stLinkCriteria = "[ClientID]=" & "'" & Me![lstCMainC] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stUserID

On the Client form:
Private Sub Form_Open(Cancel As Integer)
Dim stUserID As String
Me.vUserID = Me.OpenArgs
Select Case Me.Form.DataEntry
Case True
Me.Form.Caption = "ADD NEW CLIENT"
Me.bNewCancel.Caption = "Cancel"
Me.bEditSave.Caption = "Save"fmClientUnlockControls
fmClientDisableButtons
etc...
Case False
Me.Form.Caption = "CLIENT DETAILS"
Me.bNewCancel.Caption = "New"
Me.bEditSave.Caption = "Edit"
fmClientLockControls
fmClientEnableButtons
Case Else
End Select

This ALWAYS triggers the Case True with its resulting code. It used to work
before I added the capability to add a new client. (Used to be just Dbl
Clicking list opened the form to the correct record.)

Also, as you see above, I've set a button to Cancel when opened to Add a
record so the user can cancel an Add if they desire but that doesn' work
either. (Object doesnt support property or method error)

If Me.Dirty = True Then
Me.Undo
fmClientLockControls
fmClientEnableButtons
DoCmd.Close acForm
End If

Thank you for any help,
Robin

 

[Rod Plastow]

Robin, Hi.

I'm a little confused as to whether your second form (client details?) is a
subform contained in your main form or whether it is a separate stand-alone
form. Your description suggests a subform but your code suggests a
stand-alone form, hence I guess, the need to double-click on the list box
rather than synchronise a subform with the value of the list box.

I'll answer the stand-alone case.

First the easier of your two requirements: the command button. As you have
found out, simply open the form using the DataMode argument under the command
button's click event.

DoCmd.OpenForm "fmClient", , , , acFormAdd

You are obviously using the same command button for different action by
changing and then testing the caption. I would discourage this unless your
real estate is at a premium. Instead use two specific purpose buttons and
enable/disable them as required. However at the end of the day the design
decision is yours.

Now for that list box. You first need to make a design decision: do you want
the form to open containing one and only one record - that corresponding to
the value in the list box - or do you want to open it containing all records
but positioned at the record corresponding to the list box? In the former
case use the WhereCondition argument of to openform statement.

DoCmd.OpenForm "fmClient", , , "ClientId = " & Me.lstCMainC

or if your client id is alphanumeric

DoCmd.OpenForm "fmClient", , , "ClientId = '" & Me.lstCMainC & "'"

For the latter case you need to use the OpenArgs to pass the value of the
client id. OpenArgs is a string variable so if your client id is numeric you
need to convert it.

DoCmd.OpenForm "fmClient", , , , , , CStr(Me.lstCMainC)

I would 'catch' the OpenArgs using the second form's OnLoad event rather
than the OnOpen; OnOpen occurs before the form is loaded with data so it's
not really appropriate. You need to locate the matching record in the
underlying recordset and make it the current record. Do this using the DAO
recordset clone function and bookmarks.

[A brief digression here: dependant upon which version of Access you are
using you may not have the DAO object model referenced. v2007 references it
automatically whereas some of the earlier versions do not. If you get nasty
'user type not defined' error messages or similar then you need to reference
the DAO model manually. Ask how to do this if you don't know.]

In the OnLoad event of your second form do something like the following. [I
haven't tested this!]

Dim rstClone as DAO.Recordset
Set rstClone = Me.RecordsetClone
rstClone.MoveFirst
rstClone.FindFirst "ClientId = " & CLng(me.OpenArgs) 'If numeric id
Me.Bookmark = rstClone.Bookmark
rstClone.Close

For the sake of completeness I will mention that if you treat the second
form as an instance of an object class - which it is - and open it using OOP
techniques you can do all manner of thing to your second form directly from
the code in your main form.

Finally, if I may be so bold, I would encourage you to make a practice of
using the more widely accepted naming conventions (based largely on the
Reddick convention). The prefix for a command button is 'cmd;' for a form,
'frm;' for a string, 'str;' etc. (I too am guilty: I use 'btn' for command
button and reserve 'cmd' for ADO Command objects.)

Have fun,

Rod

 

[Rod Plastow]

Robin, a postscript.

If your Select Case statement is always True then I suspect that the 'Data
Entry' property of the form's design is persistent and set to 'Yes.' You
could check this from the form design window.

Try including the DataMode parameter of acFormEdit in your second
DoCmd.OpenForm statement to toggle it back.

Rod

 

[Robin]

Rod,

Thank you very much! Your postscript WAS the problem. When I tested the
"New Client" button it set DataEntry property to Yes and I had no method for
changing it back. I added the acEdit to the DblClick event and it works
perfectly.

The Client form is a stand alone form, opening to a ClientID when a client
is "double-clicked" and opening to a blank form (to allow data entry) when
the "New Client" button is clicked.

Your warning about using Button Captions to determine an action has me a bit
worried. I've used this on 24 buttons with up to 4 possible captions PER
BUTTON! (Real estate is very much at a premium!) I have a main form with a
series of buttons in the Form Header which change based on the tab selection
of a Tab Control (4 tabs) on the form. Each Tab (page) has a subform with
another Tab Control (3 tabs) with another series of buttons in their Form
Headers. I realize too many controls on a form can affect performance but no
real "work" takes place on the Main form, only the ability to call stand
alone forms and see various lists.

Thank you again for your helpful insight. I could have looked at that for a
week and not picked up on the DataEntry property!

Now I hate to ask, but do you have any suggestions on how to Cancel the "New
Client" action as described at the end of my original post? (Click "New
Client" -> Stand-alone form opens to allow a new record -> User starts to add
info but decides not finish -> Click "Cancel" button -> ???)

Thanks,
Robin

 

[Rod Plastow]

Hi Robin,

Glad to hear one problem is solved. The comment about using button captions
wasn't as strong as a warning, more of advice on good practice. Separate
specific purpose buttons lead to cleaner easier-to-read code. If lack of real
estate is a concern then by all means use the changing caption technique. You
might however consider introducing a hidden variable for 'mode' and test that
instead of a caption; this leaves you free to change the caption wording in
the future.

Incidentally I use the changing caption technique for wizards; the button
that reads 'Next' all the way through the wizard pages changes to 'Finish' or
'Commit' on the final page. I believe the use of two buttons in this cae to
be wasteful of space.

I remember having difficulty with the logic to cancel a record addition.
Me.Undo should work - incidentally you don't indicate which object doesn't
support the property or method - are you able to use the F8 step through
debugging facility of the VBA window?

However if I remember correctly I had to listen for a record update and play
with the form's 'Cycle' property before I got it all working. Me.Undo
obviously will not work if the record has been added and you need to take
deliberate action to delete the record from the table. You then suffer the
#Deleted display in all your controls. Autonumbers can get in the way too.

I will give it some thought and try to remember - it will be quicker for me
than searching through all those CDs and DVDs for the relevant database.

Rod

 

[Rod Plastow]

I've just played around with a few ideas. For me, in A2007 Me.Undo when
placed in the event-handler of a command button doesn't do very much but when
placed in the event-handler for the form's Before Update has the desired
effect. Let me explain and suggest a solution.

I set the following properties on my test form.

Data Entry = true
Cycle = Current Record

The latter setting prevents your user adding multiple new clients. This may
be what you want (or it may not!).

In the form's module add a local variable.

Private mbinAbandonAddition as Boolean

Under the form's Open or Load event set this variable to be false.

mbinAbandonAddition = false

For the button's click event-handler code the following.

mbinAbandonAddition = true
docmd.close acform,me.name

in the form's Before Update include

If mbinAbandonAddition then me.undo

Works for me!

Rod

 

[Robin]

Rod,

That works! (Although I'm not sure why!) I appreciate all your input on
these problems. You solved those issues and taught me some things to boot.

Thank you again,
Robin