Create Table/Record Controls Form

[Jim]

I'm converting/re-writing an Access 2.0 db to v12 (A2K7). I started working
with Access about 6 weeks ago but have about 15yrs experience with
VFP/Clipper/etc.

Since I had problems with the custom controls toolbat upgrading to even
2003, I am creating the new controls from scratch.

I was thinking about creating a new form containing only buttons
(controlling addrec, Findrec, printing, exit, etc. I could then copy this
forms' objects and paste them onto any of the other forms where I am doing
table maintenance.

Two questions...First, is this preferred method of doing this or is there a
different built-in wizard/tool that I am unawares of?

Also, rather than reinvent the wheel, can someone suggest a source of web
free/shareware where I might download prebuilt forms/code to hasten
completion of the project?

Thanks!

Jim

 

[Klatuu]

As to template databases, go to microsoft.com, find the Office sites, and
look for Access templates. There are several variaties you might find useful.

As to copying controls from form to form, be aware when you copy a control,
the code in it's events does not copy with it. In Access, all code is in
modules. There are four types of modules.
Class modules - Use to create custom class objects
Standard modules - VBA code that is common to all objects in the application
Form modules - Contains event code and any private procedures you write
specific to the form.
Report modules - Same as form modules.

Now, forms and reports do not have to have modules. They can be "lite"
objects. The advantage is they load more quicly. That is because a form's
module is not embedded in the form, but as a separate object. This is why
when copying a control, the code does not copy with it.

As to your table maintenace questions. What I have in my "bag of tricks" is
a generic form. It has all the buttons I need (custom navigation, Add,
Delete, Undo, Exit). These are all in the form footer. In the form header,
I have an unbound combo box that I use for seaching for an existing record.

To minimize the code, I have a standard module that contains the code for
all the events used by the command buttons and the combo box. I reference
the function for an event in the form control's event property. For example,
the Delete button's Click event property has =DelCurrentRec(Me) entered
directly in the Click event text box in the properties dialog for the Delete
button. It calls this generic code that can be used by any form:

'---------------------------------------------------------------------------------------
' Procedure : DelCurrentRec
' DateTime : 2/6/2006 09:07
' Author : Dave Hargis
' Purpose : Deletes a recor
'---------------------------------------------------------------------------------------
'
Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function

Now, rather than copy controls, what I have is a template form. When I need
a table maintenance form, I copy the template, give a name, then set the
record source of the new form to the table I want to maintain, Add controls
for the fields in the table, and I am done.

 

[Stuart McCall]

I'm converting/re-writing an Access 2.0 db to v12 (A2K7). I started
working
with Access about 6 weeks ago but have about 15yrs experience with
VFP/Clipper/etc.

Since I had problems with the custom controls toolbat upgrading to even
2003, I am creating the new controls from scratch.

I was thinking about creating a new form containing only buttons
(controlling addrec, Findrec, printing, exit, etc. I could then copy this
forms' objects and paste them onto any of the other forms where I am doing
table maintenance.

Two questions...First, is this preferred method of doing this or is there
a
different built-in wizard/tool that I am unawares of?

Also, rather than reinvent the wheel, can someone suggest a source of web
free/shareware where I might download prebuilt forms/code to hasten
completion of the project?

Thanks!

Jim

In addition to Dave's excellent advice re the generic form template, you
could also consider making such an object and embedding it as a subform on
any form that requires it. I've used this technique many times over the
years and find it to be very handy. The advantage is that any changes to the
generic form will propagate throughout the app.

Please don't say "but I'll never make any changes to it"

 

[Klatuu]

Please don't say "but I'll never make any changes to it"

That is exactly why all the code is in one standard module, not the form
template.

Any change made to the standard module is then inherited by all the forms.

The only code in the copied forms would be specific to the table (validation)

 

[Stuart McCall]

Please don't say "but I'll never make any changes to it"

That is exactly why all the code is in one standard module, not the form
template.

Any change made to the standard module is then inherited by all the forms.

The only code in the copied forms would be specific to the table
(validation)

<SNIP>

Same thing via a different route IMO (classic one-to-many). I've used much
the same method too, but lean more towards the subform class module.
Personal preference only.

Just pointing out another option.

Incidentally, another method I've found particularly useful is to use a
standalone class module and bind/unbind it to the form in the Open/Close
events. I can then fire my own events and sink them in the form's module,
which can speed up and simplify quite a lot of processing tasks.

 

[Klatuu]

yeah, I keep threatening to use a class module with sinking events, but it is
just getting around to doing it.

 

[Jim]

Thanks fellas.

I've already created a form and attached it to a second form as a sub-form.
Since I am quite new to Access (with roots in VFP), would it make sense to
always refer to the tables I am modifying with the same alias? (Of course I
have to make sure I remember to open and close tables properly.)

If I use the same alias (eg, CurrentTable), it would appear that the
controls ought to work transparently with whatever table is open. Along the
same lines, could I assign an alias to a report (egCurrentReport)? That way
the print button would print the right report for the matching table?

Thank!

 

[Klatuu]

You don't need to open a table. You use the Record Source property of the
form to bind the table to the form. Then you bind each field in the table to
a control on the form. Retrieving and Saving records takes no code at all.

This is the preferred way to do it.

 

[Stuart McCall]

yeah, I keep threatening to use a class module with sinking events, but it
is
just getting around to doing it.

If anyone reading this would like to explore the event firing/sinking
technique, there's a simple but useful demo here:

http://www.smccall.demon.co.uk/Downloads.htm#FsRecurse

It's yet another recursive file system scanner, but one that fires (among
other things) a FileFound event, passing useful info about the file just
encountered. The advantage is that you decide what to do with the info, not
the class instance. This means that the files are only iterated once in
order to perform any action you like on them. You could store the values in
a table, array or collection for later processing, or you could perform said
processing there and then ('on the fly').