Opening form to a specific record

[Ray Todd Jr]

Table:

taDEFENDANTS
DefendantID PK
PropertyID
DefendantsNameID

taPROCESSSERVICE
ProcessServiceID
DefendantID FK
SummonsIssueDate

Forms:

subDEFENDANTS

SELECT taDEFENDANTS.DefendantID, taDEFENDANTS.PropertyID,
taDEFENDANTS.DefendantsNameID, taDEFENDANTNAMES.LastName,
taDEFENDANTNAMES.FirstName, taDEFENDANTNAMES.MiddleName,
taDEFENDANTNAMES.Suffix, taDEFENDANTNAMES.DOB, taDEFENDANTNAMES.Address1,
taDEFENDANTNAMES.Address2, taDEFENDANTNAMES.City, taDEFENDANTNAMES.State,
taDEFENDANTNAMES.Zip, taDEFENDANTS.DefendantTypeID,
taDEFENDANTNAMES.DeceasedDate FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE
INNER JOIN ((taDEFENDANTNAMES RIGHT JOIN taDEFENDANTS ON
taDEFENDANTNAMES.DefendantsNameID=taDEFENDANTS.DefendantsNameID) LEFT JOIN
taPROCESSSERVICE ON taDEFENDANTS.DefendantID=taPROCESSSERVICE.DefendantID) ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID=taDEFENDANTS.PropertyID ORDER BY
taDEFENDANTNAMES.LastName, taDEFENDANTNAMES.FirstName;

frmPROCESSSERVICE-status

SELECT taPROCESSSERVICE.ProcessServiceID, taPROCESSSERVICE.DefendantID,
taPROCESSSERVICE.PropertyLiensID, taPROCESSSERVICE.SummonsIssueDate,
taPROCESSSERVICE.DateAssignedToServer, taPROCESSSERVICE.Served,
taPROCESSSERVICE.AnswerFiled, taPROCESSSERVICE.ContractorID FROM taDEFENDANTS
INNER JOIN taPROCESSSERVICE ON
taDEFENDANTS.DefendantID=taPROCESSSERVICE.DefendantID;

Code to Open frmPROCESSSERVICE-status from within subDEFENDANTS via a
Command Button:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPROCESSSERVICE-Status"


DoCmd.OpenForm stDocName, , , DefendantID = Me.DefendantID

Problem:

When I click on the Command Button to see the Process Service status, the
form opens up. However, the DefendantID is 0 (zero). If I were to add data
it obviously won't save to the table because there is o DefendantID defined.

Once the table has been populated, the data that is shown is the first piece
of data regardless of the referring DefendantID. When I look at the Filter
in the Data Property (In design mode), it shows a -1.

I would like the form to open up to the DefendantID that requested the form
to open and if no data currently exist to be able to add the new data.

Can someone please tell me what I am doing so wrong here?

Thanks,

Ray.

 

[Jeanette Cunningham]

Hi Ray Todd Jr

This line
DoCmd.OpenForm stDocName, , , DefendantID = Me.DefendantID

needs a little tweaking to fit in with VBA's rules.

DoCmd.OpenForm stDocName, , ,"DefendantID = " & Me.DefendantID

You need both the quotes and the ampersand.
However if DefendantID is a text field, you need more quotes like this:

DoCmd.OpenForm stDocName, , ,"DefendantID = "" & Me.DefendantID & """


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ray Todd Jr]

Hey Jeanette:

Thanks for your assistance.

I made the changes and the form opens, however, the DefendentID is still
blank. When I go to design view and look at the Filter in the data tab it
DOES contain the DefendantID that was used to open the form.

Like I said, the DefendantID is blank. Any more ideas?

Thanks,

Ray.

 

[Jeanette Cunningham]

It sounds as if your form is opening to a blank record ready for a new
record to be entered.

To open to an existing record, the form needs to be in edit mode which is
different from data entry mode.

Open the form in design view.
On the form's property dialog, on the data tab, find Data Entry and change
it to No.
Does this fix the problem?

 

[Ray Todd Jr]

Hey Jeanette:

The Data Entry is currently set to 'No'. I made no changes to it.

Here are the properties:

Form: frmPROCESSSERVICE-Status
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
DividingLines: False FetchDefaults: True

Text Box: DefendantID
BackColor: 16777215 BackStyle: Normal
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 CanGrow: False
CanShrink: False ColumnOrder: Default
ColumnWidth: Default ControlSource: DefendantID
DecimalPlaces: Auto DisplayWhen: Always
Enabled: True FilterLookup: Database Default
FontBold: No FontItalic: False
FontName: Tahoma FontSize: 8
FontUnderline: False FontWeight: Normal
ForeColor: 0 Height: 240
IsHyperlink: False KeyboardLanguage 0
:
Left: 2940 LeftMargin: 0
LineSpacing: 0 Locked: False
NumeralShapes: System OldBorderStyle: 0
ReadingOrder: Context RightMargin: 0
ScrollBarAlign: System ScrollBars: Neither
SpecialEffect: Sunken TextAlign: Center
Top: 1380 TopMargin: 0
Visible: True Width: 1440

Thanks for taking your time to look at this.....

Ray.

 

[Jeanette Cunningham]

When the form opens, does it show any data for the DefendantID that you used
in DoCmd.OpenForm?
Is DefendantID a bound field - does the control for DefendantID have a
control source?

If you build a query the same as the recordsource for the form, in design
view, put that particular DefendantID value in the criteria row for the
DefendantID field, then switch to datasheet view, can you see any records?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ray Todd Jr]

Jeanette:

See my answers below -

When the form opens, does it show any data for the DefendantID that you used
in DoCmd.OpenForm?

No, it is blank.

Is DefendantID a bound field - does the control for DefendantID have a
control source?

Yes it is bound to the DefendantID in the taProcessService table.
DefendantID is a number(long) data type.

When I change the frmPROCESSSERVICE-status to design mode and look at the
properties, data tab, under filter I see the correct DefendantID number that
I selected.

If you build a query the same as the recordsource for the form, in design
view, put that particular DefendantID value in the criteria row for the
DefendantID field, then switch to datasheet view, can you see any records?

No. If because there are no records for that particular DefendantID. But
would like to add a record for it but the DefendantID isn't being brought
forward to the DefendantID.

If I put in a DefendantID where there is a record, I DO get the correct
record.

Again, thanks for all your assistance.

Ray.

 

[Jeanette Cunningham]

To open a form and show an existing record, you use
DoCmd.OpenForm stDocName, , , "[DefendantID] = " & Me.DefendantID


To open a form to add a new record, you can use
DoCmd.OpenForm stDocName
and on the form that is being opened, you put code in its open or load event
to make it go to a new record.

Private Sub Form_Load()
DoCmd.GoToRecord, , acNew
End Sub

You will not see the new value for DefendantID until you enter something
into one of the fields on the form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ray Todd Jr]

Jeanette:

Nope, I cannot get it to work. I have reposted my message with hopfully
more clearly described information. After reading my post, I'm not sure that
I made clear what I was trying to do or what my problem was/is.

Thanks for trying to help me....

Ray.

To open a form and show an existing record, you use
DoCmd.OpenForm stDocName, , , "[DefendantID] = " & Me.DefendantID


To open a form to add a new record, you can use
DoCmd.OpenForm stDocName
and on the form that is being opened, you put code in its open or load event
to make it go to a new record.

Private Sub Form_Load()
DoCmd.GoToRecord, , acNew
End Sub

You will not see the new value for DefendantID until you enter something
into one of the fields on the form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette:

See my answers below -



No, it is blank.


Yes it is bound to the DefendantID in the taProcessService table.
DefendantID is a number(long) data type.

When I change the frmPROCESSSERVICE-status to design mode and look at the
properties, data tab, under filter I see the correct DefendantID number
that
I selected.


No. If because there are no records for that particular DefendantID. But
would like to add a record for it but the DefendantID isn't being brought
forward to the DefendantID.

If I put in a DefendantID where there is a record, I DO get the correct
record.

Again, thanks for all your assistance.

Ray.