Form/Subform - No Advance if no subform entry 1

[BobJacksonNCI]

Hello!

I previously posted - thread958-733421 - but still have the problem that Users can use navigation buttons (standard) to advance to a new record repeatedly with no subform records created. This leaves orphans in the "one" table.

Environment = Access 2000 Project with SQL 7 engine.

Forms are used to enter new records and view/edit existing records.

Using a Form populated based on Select statement with parameter from another form (employee selection). Subform uses a stored procedure with parameter to maintain synchronization with the main form.

Example: User wants to enter a new Time Off request.
Selects employee in selection form.
Selects button that brings up Time Off form/subform.
(Each form can have up to eight subform records.)
Logic finds existing Time Off records and presents the most recent one.
User uses > button, or >* button until form with blank subform appears.
User inadvertently selects > again, resulting in a new form record with blank subform (ready for data entry).
BUT, there has already been stored a form record with no subform record - which is what I want to avoid.

I started experimenting with creating my own navigation buttons. These are on the form, so this approach will likely require working with a recordset clone - which I use in the subform for validation.

But I'm wondering if I can't manipulate the Allow Additions property to stop these unwanted orphans. Keep in mind that I would prefer having navigation buttons at the form level only...

Sorry for the long read - trying to provide all information that is needed.

Thanks in Advance!

 

[TheAceMan1]

How are ya BobJacksonNCI . . . . .

Just an Idea off the top of my head, you could make a control on the subForm [blue]Required![/blue] just set it in the specific table. This should cancel advancing to a new record & bring up a Required Notice Msg.

If that doesn't appeal to you, try the following:

Disable [purple]AllowAdditions[/purple] when a new subForm record is detected by putting the following code in the [blue]On Current[/blue] event of the MainForm:

[blue]Dim sfrm As Form
Set sfrm = Me![b][u]YoursubFormName[/u][/b].Form
If sfrm.NewRecord then 
   Me.[purple]AllowAdditions[/purple] = False
Else
   Me.[purple]AllowAdditions[/purple] = True
End If[/blue]

Then in any control on the subForm that [blue]Requires[/blue] data, turn it back on with:

[blue]Dim frm As Form
Set frm = Forms![b][u]YourMainFormName[/u][/b]
frm.[purple]AllowAdditions[/purple] = True[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

[blue]User uses > button, or >* button until form with blank subform appears[/blue]

If the user is stepping thru the subform, I'm gonna have to change the code. I presently sset code for stepping thru Main Form.

Let Me know!

See Ya! . . . . . .

 

[BobJacksonNCI]

Hi AceMan1!

Thanks for working with me on this!!

Users step thru (navigate) on the main form.

There are combinations of controls on the subform that are required (being validated now), but no individual control requires input on its own.

I'm testing the allowadditions true/false, but am having trouble with the compiler. (Apparently, it wants syntax that differs from that which I am presenting.)

ACTUAL CODE WITH CORRECT, IF UNIMAGINATIVE, SUBFORM NAME
========================================================
Private Sub Form_Current()

'FOLLOWING TO AVOID ALLOWING NEW FORM RECORD W/O SUBFORM RECORD
Dim sfrm As Form
Set sfrm = Me!frm_TIME_OFF_SUBFORM.Form

'If Me!frm_TIME_OFF_SUBFORM.Form.NewRecord Then
If sfrm.NewRecord Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
========================================================

Getting run-time error '2455'
You entered an expression that has an invalid reference to the property Form/Report

Maybe the format should be:
Me!frm_TIME_OFF_SUBFORM.Form.NewRecord

Or:
Maybe I don't need to Dim + sfrm if I write out the reference correctly.

Testing as we speak,
Bob

 

[TheAceMan1]

BobJacksonNCI . . . .

Hmmmmm . . . I pretested this . . .. . maybe your missing a reference . . . .

In any code module window: MenuBar > Rferences. Your looking for [blue]Microsoft DAO 3.6 Object Library[/blue]. It should have a check. If it doesn,t, make it so. In any case, push it as high in priority as it will go.

Let me know . . .

See Ya! . . . . . .

 

[BobJacksonNCI]

Hey AceMan1,

Interesting...I moved the already-checked reference to the DAO 3.6 Library in front of OLE Automation and Activex Data Objects 2.1 Library.

When I compiled, a routine to identify Users logged on to the database errored out. When I moved the reference back, it compiled... That routine uses an ADO recordset. And it's not required in this database so I deleted the routine, moved the DAO reference up in priority, recompiled and executed.

Got the same error message as before on the bold line:

Private Sub Form_Current()

'FOLLOWING TO AVOID ALLOWING NEW FORM RECORD W/O SUBFORM RECORD
Dim sfrm As Form
Set sfrm = Me!frm_TIME_OFF_SUBFORM.Form

'If Forms!frm_TIME_OFF!frm_TIME_OFF_SUBFORM.Form.NewRecord = True Then

If sfrm.NewRecord Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

For grins, I unchecked the reference to ActiveX 2.1, but a routine that IS needed for pass-through queries failed compilation.

Your help IS appreciated!

Unfortunately, I have to leave now. Will check back in case you post either at home, or tomorrow morning.

Bob

 

[PHV]

frm_TIME_OFF_SUBFORM should be the name of the control hosting the subform.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BobJacksonNCI]

PHV,

Thanks for replying!

Uh, how do I find the name of the control hosting the subform?

Thanks!
Bob

 

[TheAceMan1]

BobJacksonNCI . . . .

Can't see any reason yet for failure. So lets start at ground zero and be sure of the Form/subForm names (not what it saids in the Labels!).

For each form, call up the properties - select the [blue]Other Tab[/blue]. At the top is the [blue]Name Property[/blue] ([purple]the actual name used in VBA[/purple]).

What are the Names for the Form & subForm (double-check the spelling!)?

Also what line is the debugger hilighting when if fails?

See Ya! . . . . . .

 

[BobJacksonNCI]

Good Morning!

Form Name = frm_TIME_OFF

Subform = frm_TIME_OFF_Subform

When compiled, the Subform name is automatically set to all capitals - frm_TIME_OFF_SUBFORM

Unless I misunderstand incorrectly, Properties / Other shows the VBA name for controls, not for forms/subforms.

Thanks again!
Amazing that it works for you and not me.

What version are you using?
I've got Access 2000 hooked up to SQL 7.
Not optimum, but out of my control.

Bob

 

[RoyVidar]

There are two important properties when dealing with subforms.

The subform name, which is the name we observe when viewing the forms in the database window

The subform control name, which is what we use in VBA to refer to the form control.

A subform is not seen as an open form, but as a control on the main form.

When something in the main form is selected when in design view, one click on the subform, should provide you with the properties of the subform control. The title part of the property dialog should display "Subform / Subreport SubFormControlName". Entering the other tab, view the .Name property, the subform control name is to be viewed. This is what we use when referring to the subform in VBA, and it might differ from the subform name, which is viewable in the data tabs "Source Object" property. It is important not to mix those.

For more information, take a look at this Microsoft article How to Refer to a Control on a Subform or Subreport

An easy way of obtaining the correct reference is for instance to enter the controlsource property of a control in the main form, use the expression builder, doubleclick thru forms, loaded forms, main form, subform and a control on the subform. You should get somethin like this:

[tt][SubFormControlName].Form![txtControl][/tt]

Alter to fit the statements above.

Roy-Vidar

 

[RoyVidar]

As another note, have you by any chance used spaces in the name? Sometimes VBA changes spaces to underscore, but they won't work.

If so, try altering to
[tt]Me![frm TIME OFF Subform].Form[/tt]

And, re the ADO/DAO thingie, my advice would be to not rely on the order of the references (because, in my experience, it will sooner or later bomb), but explicitly declare each object variable specifying the library:

[tt]dim rs as ADODB.Recordset
dim cnn as ADODB.Connection
dim rs2 as DAO.Recordset
dim db as DAO.Database[/tt]

Roy-Vidar

 

[BobJacksonNCI]

Roy,

Thanks for sharing your knowledge!

I won't be able to look at this until after lunch EDT.

Just wanted to let you know.

Bob

 

[TheAceMan1]

[blue]Unless I misunderstand incorrectly, Properties / Other shows the VBA name for controls, not for forms/subforms[/blue]

Your right when it comes to [blue]Form[/blue], but [blue]subForm[/blue] is a control as far as [blue]Parent Form[/blue] is concerned. I just want us to be clear on the names. I'm sure you know how much they matter.

[blue]Amazing that it works for you and not me.[/blue]

I'm using 2K without SQL7. It should'nt matter since [blue]Form Objects[/blue] have nothing to do with SQL7.

Since my last post, I created three other secenarios, linked & nonlinked subform, and seperate form. I adjusted my objectivity as required and [purple]No Problemo[/purple]. So I'm now just as interested as you, why this [blue]Reference[/blue] problem exist. I'm tracking down other possibilites at present and will post when I find something worthwhile. Mean while I'm interested in your results with [Blue]Roys[/blue] suggestion.

See Ya! . . . . . .

 

[BobJacksonNCI]

Problem unresolved - posting to let you all know I'm still breathing.

I'm pretty sure the form is frm_TIME_OFF and the subform is frm_TIME_OFF_SUBFORM.

I even verified those are letter O's and not zeros in the names.

It's slightly iffy because I'm working with Microsoft's first release of Microsoft Access Project connected to SQL 7.
For example, it is difficult for me to even initiate expression builder. I wanted to verify the subform name that way but cannot:

ACC2000: Expression Builder Is Not Available in Visual Basic Environment

http://support.microsoft.com/default.aspx?scid=kb;en-us;245181&Product=acc2000

Both the Name and the Source Object indicate:
frm_TIME_OFF_SUBFORM
I'm thinking Of changing the name to see if that helps.

Further evidence at early bugs is documented in a chat session that included Bill Ramos, the Lead Program Manager.

http://msdn.microsoft.com/chats/office/office_092602.as...

Telling excerpt from that chat follows:

Q: AndyG: Are there major functional or procedural differences for accessing SQL from Access XP vs. Access2K?

Host Guest_Bill_Ramos_MS
A: Huge. Access XP now has extended property support against SQL Server 2000. This gives you lookup support, caption text, all the goodies that you get against Jet. We also have a new stored procedure designer for making action queries.

Host Guest_Bill_Ramos_MS
A: cont. - We also fixed a bunch of bugs with regards to maintaining cursors in your application when changing a sort or filter condition.

Host Guest_Bill_Ramos_MS
A: cont. - If you want to use ADPs, you really need to use Access 2002 (XP)


Lest it be thunk that I'm just griping, I DO want to resolve my problem. And I've recommended installing software upgrades, but meanwhile I want to get this finished.

Thanks to all who have looked at this and especially to AceMan1 and RoyVidar!

Bob

 

[RoyVidar]

First, I've only addressed the form/subform referencing (well, and qualifying object variable declaration including library reference where different libraries have objects sharing name but not properties and methods). I've got very little experience with ADP, have just started playing with it on a box with xp. I don't think form/subform referecing should be any different in a2k vs axp. However, if you feel this is an ADP issue, I'd suggest revisiting the ADP forum.

It doesn't seem the expression builder exists in ADP, one would usually invoke it from the controlsource of a control (forms design view) or the criteria row in a query, but I don't seem to find anything but a disabled "Build..." on right click.

But - use the methods described above to find out what is the subform control name, and try different approaches to getting the correct reference. Use a control on the subform, and try for instance the immidiate pane (CTRL+G) with different variations over the theme

[tt]? Forms!FormName!SubFormControlName.Form!txtControl[/tt]

to find a syntax that returns the value currently in one of the controls. Then try referencing properties of the form. The form will need to be open, and the relevant control should also contain a value.

Or, try creating a button with some referencing behind (use msgboxes?) and try out different references including the Me keyword

Then to the logic.
The on current event of a form fires, not when you leave one record, but when you “enter” a new record (in the meaning either a new record, or an existing one). So the on current event would not trap what you intend to trap.

When triggering things from a main form (after entering subform records or not), when clicking in the main form, the records in the subform are saved, so the .NewRecord property will have little value in this test.

Now the next depends on your setup. If I've understood correctly, you're using an ADP. Then the form recordsets are ADO, so if you're doing some set rs=me.recordsetclone, be sure the rs is declared as an ADO recordset (would you need DAO at all, here?).

So how to determine when you move from one record to the next -> use custom navigation buttons, and check the number of records in the subform. For instance, if the recordset supports it, one could try the .RecordCount property of the recordsets clone:

[tt]dim rs as adodb.recordset
set rs=me!frmMain!frmSub.Form.Recordset.Clone
if rs.recordcount>0 then
docmd.gotorecord acDataForm, me.name, acNext
else
msgbox "enter records before..."
end if[/tt]

- or just try testing the me!frmMain!frmSub.Form.Recordset.RecordCount – or something similar. Did try out ADO form recordset on ordinary a2k, where it seems the RecordsetClone property does'nt work, but the clone method of the recordset did (as in the above sample).

If the recordset does not support .recordcount, then try looping thru the recordset and count the records.

- the last issues, will probably be better addressed in the ADP forum.

So – it still remains to find the correct reference to the subform...

Roy-Vidar

 

[BobJacksonNCI]

Thanks, Roy! (star-worthy)

That takes care of the posted question, but not quite finished tuning.

Suggested code, following, stops appropriately when the custom navigation button to advance on the form is pressed and there are no records for the current subform. However, when the Close button is selected after that, its message regarding orphan records is displayed. But, after closing the form and reopening for the same employee, form with recordless subform is still present. (If the Close button is selected WITHOUT invoking the navigation button code, the orphan record IS deleted.)

NOTE that the Close button code HAS been working as desired otherwise.

Ideas?
Bob

CUSTOM NAVIGATION (ADVANCE) BUTTON CODE:
Private Sub cmdNavNext_Click()
On Error GoTo Err_cmdNavNext_Click

Dim rs As ADODB.Recordset
Set rs = Forms!frm_TIME_OFF!frm_TIME_OFF_SUBFORM.Form.RecordsetClone

If rs.RecordCount > 0 Then
DoCmd.GoToRecord acDataForm, "frm_TIME_OFF", acNext
Else
MsgBox "Selected button to advance to next form with no time off requested on this form. " & Chr(10) & _
"Action canceled.", vbOKOnly, "ADVANCE TO NEW FORM NOT ALLOWED"
End If

Exit_cmdNavNext_Click:
Set rs = Nothing
Exit Sub

Err_cmdNavNext_Click:
MsgBox Err.Description
Resume Exit_cmdNavNext_Click

End Sub


CLOSE BUTTON CODE
Private Sub cmdCLOSE_Click()
On Error GoTo Err_cmdCLOSE_Click

'IF THE HELP WINDOW IS OPEN, CLOSE IT
stDocName = "~fhlp_TIME_OFF"
If SysCmd(acSysCmdGetObjectState, acForm, stDocName) = acObjStateOpen Then
DoCmd.Close acForm, stDocName
End If

'IF NEW RECORD ON MAIN FORM AND NO RECORDS ON SUBFORM
'DON'T SAVE PRELOADED RECORD ON MAIN FORM
Dim stSQLCommand As String
Dim rsetNumRecords As ADODB.Recordset

stSQLCommand = "ALTER VIEW rjackson.view_Time_Off_Chk4Orphans AS "
stSQLCommand = stSQLCommand & "SELECT * FROM dbo.MgrTB_tbl_Time_Off_Detail WHERE dbo.MgrTB_tbl_Time_Off_Detail.FormID_Many = '" & [Forms]![frm_TIME_OFF]![txtFormID_One] & "' "
ADOSQLPassThrough stSQLCommand

Set rsetNumRecords = ADOSQLPassThrough("SELECT Count(*) FROM rjackson.view_Time_Off_Chk4Orphans")
rsetNumRecords.MoveFirst

If rsetNumRecords(0) = 0 Then

stSQLCommand = "ALTER PROCEDURE rjackson.MgrTB_spr_Time_Off_Delete AS "
stSQLCommand = stSQLCommand & "DELETE FROM dbo.MgrTB_tbl_Time_Off WHERE dbo.MgrTB_tbl_Time_Off.FormID_One = '" & [Forms]![frm_TIME_OFF]![txtFormID_One] & "' "
ADOSQLPassThrough stSQLCommand

stSQLCommand = "Execute rjackson.MgrTB_spr_Time_Off_Delete"
ADOSQLPassThrough stSQLCommand

MsgBox "Deleted form because there was no Date Requested entry", vbOKOnly, _
"CLOSE WITH NO DATE REQUESTED"

End If

DoCmd.Close acForm, "frm_TIME_OFF", acSaveNo

Exit_cmdCLOSE_Click:
Set rsetNumRecords = Nothing
Exit Sub

Err_cmdCLOSE_Click:
MsgBox Err.Description
Resume Exit_cmdCLOSE_Click

End Sub

 

[RoyVidar]

Thanx - I find it amusing, interesting and challenging to do forms programming - and this has been an interesting challenge!

But I'm afraid I'm at loss here. ADP's are still pretty much an unexplored field to me, so the quessing I'm doing here, is based on "Access".

Only thing I can think of, is the state of the current record, but then there shouldn't be any consistency in behaviour between "clicking both buttons" and only the close button. So what I'm basically saying is that there might be a possibility that when trying to delete, the record isn't saved, so there's nothing to delete, but then as the form closes, the (undeleted;-)) record is saved - could that be it?

Try some tests on the following properties:
[tt]Me.Newrecord[/tt] - unsaved new record
[tt]Me.Undo[/tt] - undo changes (would "delete" an unsaved record)
[tt]Me.Dirty[/tt] - true if there are unsaved changes to the record

Having the status newrecord would mean that undo would "delete" it. For other statuses, the delete operation should be performed.

BTW - the acSaveNo argument when closing a form works on design changes, not changes in the record.

Roy-Vidar

 

[BobJacksonNCI]

More thanks - Roy,

I'm on my way out the door, but this is getting close to working.

The code for the Close button was written for me by someone else. Further idiosyncracy:

I haven't found an explanation for exactly what

If rsetNumRecords(0) = 0 Then

is doing. So, I substituted:

If rsetNumRecords.BOF and rsetNumRecords.EOF Then

and THAT doesn't give the MsgBox message...

So, I think your last suggestions ARE where I need to concentrate.

Bob

 

[RoyVidar]

The first statement seems to check what's returned in the first field in the resulting recordset when calling the passthrough query [tt]"SELECT Count(*) FROM rjackson.view_Time_Off_Chk4Orphans[/tt]", which is probably just another way of obtaining the recordcount of the subform. Just curious, in Access one usually do "Select Count(*) as somenewfieldname from..."

Changing that line to testing for whether the recordset is empty - then that's testing for something quite different. The count query should return a value nomatter what, so the routine would never enter the "delete part".

If you feel like changing that test, then rather change it to something like the test performed in the custom navigation button.

Roy-Vidar