new subform entry for existing record

[Karen]

Hi
I have my mainform with 2 subforms. If a record in the main table already
exists, it can have multiple events attached to it in other tables. Using the
form, if a user enters an already existing primary key the form will
automatically call up data in the subforms. However the mainform does not
bring up the rest of the information from the main table so data entry cannot
continue (due to primary key and duplication). If I search (ctrl+f) on the
primary key field to find the already existing record, I can create new
records in the subforms.
With large volumes of records, a user is not going to know if a record
already exists or not...how can I get the form to bring up all of the info if
the user enters a pre-existing primary key (and allow more records to be
created in the subforms.)
Thanks
Karen

 

[John W. Vinson]

Hi
I have my mainform with 2 subforms. If a record in the main table already
exists, it can have multiple events attached to it in other tables. Using the
form, if a user enters an already existing primary key the form will
automatically call up data in the subforms. However the mainform does not
bring up the rest of the information from the main table so data entry cannot
continue (due to primary key and duplication).

What you're doing there is OVERWRITING AND DESTROYING the primary key value of
whichever record happened to be on the screen.

If I search (ctrl+f) on the
primary key field to find the already existing record, I can create new
records in the subforms.
With large volumes of records, a user is not going to know if a record
already exists or not...how can I get the form to bring up all of the info if
the user enters a pre-existing primary key (and allow more records to be
created in the subforms.)
Thanks
Karen

Use a Combo Box on the mainform; use the toolbox combo wizard to create it,
and use the option "Use this combo to locate a record".

 

[Karen]

Thanks, it works of course, but I'm going to have very large numbers of
records. Most data entry records will be completely new and only once every
now and again will there be an addition to a pre-exisiting record. Is there
an alternative method for this type of situation?
Thanks again
Karen

 

[John W. Vinson]

Thanks, it works of course, but I'm going to have very large numbers of
records. Most data entry records will be completely new and only once every
now and again will there be an addition to a pre-exisiting record. Is there
an alternative method for this type of situation?

You can use the BeforeUpdate event of the control which would receive the
possible duplicate ID. Use DLookUp to check whether the entered value already
exists and give the user the option of jumping to that record:

Private Sub IDfield_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
Dim rs As DAO.Recordset
If Not IsNull(DLookup("IDfield", "tablename", "IDfield = " & Me!IDField) Then
Cancel = True ' cancel the addition in any case
iAns = MsgBox("ID " & Me!IDfield & " already exists. " & _
"Jump to existing record or cancel this addition?", vbYesCancel)
Select Case iAns
Case vbYes
Set rs = Me.RecordsetClone
rs.FindFirst "IDfield = " & Me!IDField
Me.Bookmark = rs.Bookmark ' jump to the record
Set rs = Nothing
Case vbCancel
Me.Undo ' erase the form
End Select
End If
End Sub