Forms for Adding and Editing

[oldblindpew]

This is the age-old problem of wanting to use the same form for
Add/Delete/Update/Browse. I'm using an autonumber surrogate key, but the
user identifies the record by FirmName and FirmCity. I use a dialog form
with a ListBox displaying FirmName and FirmCity to allow the user to find an
existing record for a particular Firm. The user cannot be allowed to edit
the FirmName field on this form because it is in effect a key value. If the
user does not find a record for the desired firm, he needs to be able to add
a new record, picking the FirmName from an existing list. I'm thinking of
adding a button to the form, labelled "Add New Record". On click, this would
open a dialog form with a List Box showing all the firms in alphabetical
order. The user would scroll down and select the desired Firm, and a new
empty record would be added for this firm. The user could then proceed with
filling out the form for the new record.

My questions are, First, is this good practice, or is there some better way.
Second, almost everything I've done so far has been with event procedures.
Should I try doing this with a macro? Third, if I use an event procedure,
what command would I use to add the new record?

I'm sorry to ask such basic questions. I have two thick books on Access and
I can't seem to find straightforward answers in either of them.

Thanks

 

[Allen Browne]

It's quite common to use one form for adding, deleting, editing, and
browsing. No problem.

If FirmName and FirmCity in combination is crucial for you and should be
unique, you might want to open your table in design view, set the Requried
property to Yes, and create a unique index on the combination of the two.

If you have no more than a few thousand firms, you could add an unbound a
combo box to the form header, and use it to jump to the desired record. I
think you'll find this quicker to use than opening another dialog form. The
combo's RowSource might be something like this:
SELECT FirmID, FirmName & " - " & FirmCity AS TheFirm
FROM tblFirm
ORDER BY FirmName, FirmCity, FirmID;
Set Column Count to 2, and Column Widths to 0, and will display the firm
name + city combination. The code will be something like this:
http://allenbrowne.com/ser-03.html

Event procedures are much more powerful than macros.

If you don't want to allow the user to modify the firm name or city for
existing records, you could use the Current event of the form to lock them:
Private Sub Form_Current()
Dim bLock as Boolean
bLock = Not Me.NewRecord
If Me.FirmName.Locked <> bLock Then
Me.FirmName.Locked = bLock
Me.FirmCity.Locked = bLock
End If
End Sub

Alternatively, you could use the BeforeUpdate event procedure of the *form*
to give a warning if they are changing a name. This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim bWarn As Boolean

If IsNull(Me.FirmName) Then
Cancel = True
strMsg = strMsg & "Firm name required." & vbCrLf
End If

If IsNull(Me.FirmCity) Then
Cancel = True
strMsg = strMsg & "City required." & vbCrLf
End If

If (Cancel) OR ((Me.FirmName = Me.FirmName.OldValue) _
AND (Me.FirmCity = Me.FirmCity.OldValue)) Then
'do nothing
Else
bWarn = True
strMsg = strMsg & "You are changing this record from:" & vbCrLf & _
vbTab & Me.FirmName.OldValue & " - " & Me.FirmCity.OldValue & _
vbCrLf & "to:" & vbCrLf & _
vbTab & Me.FirmName & " - " & Me.FirmCity & "." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid data"
ElseIf bWarn Then
strMsg & strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Are you sure?") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End Sub

Finally, if you want a command button to add a new record:
Private Sub cmdNew_Click()
If Me.Dirty Then Me.Dirty = False 'save any edits
RunCommand acCmdRecordsGotoNew
End Sub
Add error handling: the attempt to save could fail (e.g. if a required field
is blank.)

 

[oldblindpew]

Thanks for your reply.

For now I'm sticking with my Dialog Form with List Box approach for finding
a record, but there is a problem with it. The Row Source for the List Box
includes the autonumber key field (aIDFirm) and the firm name (tNameFirm).
I've had the control (lstFirm) bound to the firm name (column 2). In my
event procedure, I assign the firm name to a string variable (strSelect) and
then find the record with DoCmd.FindRecord strSelect. The problem is,
everything is centered on the firm name, which isn't necessarily unique, as
when the firm has offices in various different cities. No matter which
duplicate the user picks, Access finds the first one listed. I need to be
searching based on the true key field, not the firm name. So I changed the
bound column from 2 to 1, but of course the event procedure didn't like this.
This is the code that doesn't work:

Dim strSelect As String
strSelect = Me!lstFirm
DoCmd.FindRecord strSelect

I tried to convert the string variable to numeric, guessing at the syntax,
but guessing is pretty hopeless.

Related subject: I'm using a button as discussed for allowing user to Add a
new record, but I can't figure out (syntax again) how to assign the selected
firm to the new record. Specifically, I need to write the primary key value
from the selected record in the Firms table to the foreign key field in the
Certificates table. (There is a one-to-one relationship between Firms and
Certificates, but I have the data in separate tables because there are so
many fields).

Thanks again and again

 

[Allen Browne]

A1: The code in this link illustrates how to find a record based on the
(hidden) ID value:
http://allenbrowne.com/ser-03.html
It's identical for combos and list boxes.

Note that the list box has a Bound Column (I think that's what you changed),
but it must be unbound (nothing in its Control Source.)

The 2nd last line of the artlcle comments on the difference between Text and
Number fields.

A2: In the form you use to add a new record to the Firms table, use the
AfterInsert event to add a new record to the Cert. table as well. In this
event, Execute an Append query statement, e.g.:
Dim strSql As String
strSql = "INSERT INTO ...
CurrentDb.Execute strSql, dbFailOnError

You can mock up a query that uses a fake value such as 99 in query design,
change it to an Append query (Append on query menu), and then swtich to SQL
View. This shows you an example of the string you need to create. Then break
the string and insert the number you want, e.g.:
strSql = "INSERT ... " & Me.FirmID & " ...

If this is a new concept,s there's more about Execute here:
http://allenbrowne.com/ser-60.html