Opening Form To Add New Records + View Existing Records

[Ray Todd Jr]

I have requested help previously and Jeanette has kindly and diligently tried
to help me, however, I'm not sure that I am providing all of the needed
information to identify where my coding error is causing the problem.

I have a subform (subDEFENDANTS) that list the individual defendants. There
is a text box that indicates if a particular defendant has been served. By
clicking on this text box, the user can either view the existing data about
the service -or- add a record about the service. Here is the code I am using:

Dim stdoc As String
Dim stdoc1 As String

stdoc = "frmPROCESSSERVICE-status"
stdoc1 = "frmPROCESSSERVICE-statusadd"

If DCount("DefendantID", "taPROCESSSERVICE",
"DefendantID=forms!frmMAIN!subDEFENDANTS!DefendantID") > 0 Then
DoCmd.OpenForm stdoc, , , "DefendantID =" &
Forms!frmMAIN!subDEFENDANTS!DefendantID
Else
DoCmd.OpenForm stdoc1, , , , acFormAdd, acDialog, "DefendantID=" &
Forms!frmMAIN!subDEFENDANTS!DefendantID
End If

The record source for each of form frmPROCESSSERVICE-status is:

ProcessServiceID Autonumber taProcessService
DefendantID Number (Long) taProcessService
SummonsIssueDate Date (Short) taProcessService
(other fields not mentioned)

The problem that I am having:

If I add a new record, the DefendantID is never filled in with the correct
DefendantID (always remains blank) even after I enter data in the other
fields.

The properties for:

frmPROCESSSERVICE-status (to view existing data)

Properties
AllowAdditions: True AllowDatasheetView: True
AllowDeletions: True AllowEditing: True
AllowEdits: True AllowFilters: True
AllowFormView: True AllowPivotChartView: True
AllowPivotTableView: True AllowUpdating: No
AutoCenter: False AutoResize: True
BorderStyle: Sizable CloseButton: True
ControlBox: True DataEntry: False
DefaultEditing: 2 DefaultView: Single Form

frmPROCESSSERVICE-statusadd (to add a record)

AllowAdditions: True AllowDatasheetView: True
AllowDeletions: True AllowEditing: True
AllowEdits: True AllowFilters: True
AllowFormView: True AllowPivotChartView: True
AllowPivotTableView: True AllowUpdating: No
AutoCenter: False AutoResize: True
BorderStyle: Sizable CloseButton: True
ControlBox: True DataEntry: True

This is obviously an error that I am making but cannot identify where I am
making such mistake.

Any help or suggestions is much appreciated....

Thanks,

Ray.

 

[Jeanette Cunningham]

A question.
Do you click a button on the main form to open the form stdoc1 so you can
edit details for the defendantID?

To get the value of a control on a subform when you are on the main form,
you need to use very specific syntax.

Me![NameOfSubformControl].Form!DefendantID

The reference you were using would not return the value for DefendantID.

Note: [NameOfSubformControl] is often different from the name of the subform
inside the subform control.

Open your main form in design view.
Click once on the subform, so only its border is selected - see the
selection handles.
On the property sheet you can see both the subform control name - on the
Other tab,
and the name of the subform - on the Data tab as the source object.

Hope that helps.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ray Todd Jr]

Jeanette:

See below:

A question.
Do you click a button on the main form to open the form stdoc1 so you can
edit details for the defendantID?

It is a text box that is on the subDEFENDANTS form.

I will attempt your suggestions.

Thanks,

Ray.

 

[Jeanette Cunningham]

Another question/s.
Do you click the text box that is on the subDEFENDANTS form?
Are you trying to open the form to add a new defendant?
The code I sent before was to use if you clicked a button on the main form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ray Todd Jr]

Hey Jeanette:

See below -

Another question/s.
Do you click the text box that is on the subDEFENDANTS form?

Yes, the text box, txtServiceFlag, is on the subDEFENDANTS form, which is on
the form frmMAIN.

Here is the code as currently written for the txtServiceFlag text box when
clicked:

Private Sub txtServiceFlag_Click()

Dim stdoc As String
Dim stdoc1 As String

stdoc = "frmPROCESSSERVICE-status"
stdoc1 = "frmPROCESSSERVICE-statusadd"

If DCount("DefendantID", "taPROCESSSERVICE",
"DefendantID=forms!frmMAIN!subDEFENDANTS!DefendantID") > 0 Then
DoCmd.OpenForm stdoc, , , "DefendantID =" &
Forms!frmMAIN!subDEFENDANTS!DefendantID
Else
DoCmd.OpenForm stdoc1, , , "DefendantID =" &
Forms!frmMAIN!subDEFENDANTS!DefendantID, acFormAdd
End If

End Sub

Are you trying to open the form to add a new defendant?
The code I sent before was to use if you clicked a button on the main form.

This is what I am trying to achieve.

1. If a record exist for a particular defendant in the taProcessService
table I want to see the details of the record.

2. If a record does *NOT* exist for a particular defendant in the
taProcessService table I want to be able to add a record.

As it is right now, if a record does not exist in the taProcessService table
for a DefendantID, it will open the correct form, however, a DefendantID is
never filled in so, the link is never made between the two tables as the
DefendantID is blank.

Again, thanks for all of your help.

Ray.

 

[Jeanette Cunningham]

Opening a form to add a new record is quite different from opening the same
form to edit a record.
Sometimes it is easier to manage if you have one form for edits and another
for new records.
However, we will do the case where you use the same form for both.

Dim strArgs as String
strArgs = Forms!frmMAIN!subDEFENDANTS!DefendantID
DoCmd.OpenForm stdoc1, , , , acFormAdd

In the above code, we are passing the value for DefendantID in the open
args.
You simply can't open a form at a new record and at the same time open it to
a particular value for DefendantID.

Once stdoc1 is open, it can read the value for DefendantID from the open
args and save that value for later use.
I will assume you know how to use open args in the load or open event for
stdoc1.
So you get lngID = Me.OpenArgs
Then you can go Me.DefendantID.DefaultValue = lngID

If you don't want use default value, you can just go
Me.DefendantID= lngID

Another way is to use a sql statement to add a new DefendantID to the
Defendant table - you add a new record to the table, get the value of the
newly added DefendantID, then just open the form the same as if you were
doing an edit - except that the fields are empty, except for the
DefendantID.



Private Sub txtServiceFlag_Click()

Dim stdoc As String
Dim stdoc1 As String

Dim strArgs as String


stdoc = "frmPROCESSSERVICE-status"
stdoc1 = "frmPROCESSSERVICE-statusadd"

If DCount("DefendantID", "taPROCESSSERVICE",
"DefendantID=forms!frmMAIN!subDEFENDANTS!DefendantID") > 0 Then
strArgs = Forms!frmMAIN!subDEFENDANTS!DefendantID
DoCmd.OpenForm stdoc, , , "DefendantID =" &
Forms!frmMAIN!subDEFENDANTS!DefendantID
Else
DoCmd.OpenForm stdoc1, , , , acFormAdd, strArgs
End If

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia