append then open record

  • Thread starter Thread starter Christopher Glaeser
  • Start date Start date
C

Christopher Glaeser

I implemented a FAX button that appends a record to tblFaxLog and then opens
frmFaxLog. Here is the code for btnFax_Click:

Dim stDocName As String
Dim stLinkCriteria As String
Dim cmd As ADODB.Command
Dim longRecs As Long

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryFaxLogAppendWorkOrderRealtor"
cmd.Execute longRecs, Array(Me.WorkOrderID)
stDocName = "frmFaxLog"
DoCmd.OpenForm stDocName, , , stLinkCriteria

How do I open frmFaxLog at the newly appended record?

Best,
Christopher
 
Guessing that WorkOrderID might be the (numeric) primary key of your
tblFaxLog, perhaps you could try something like this:
stLinkCriteria="WorkOrderID =" & Me.WorkOrderID
 
Guessing that WorkOrderID might be the (numeric) primary key of your
tblFaxLog, perhaps you could try something like this:
stLinkCriteria="WorkOrderID =" & Me.WorkOrderID
Click to expand...

Thanks for the quick response. WorkOrderID is a foreign key and there may
be multiple faxes associated with a WorkOrder. tblFaxLog does have a
primary key FaxLogID that is determined with an autonum when the fax log
record is appended using the action query. How do I access the new FaxLogID
when the record is appended? Can the cmd.Execute method return the value of
the new FaxLogID?
 
Hmmm ...
Not so easy, that way -
especially if you are operating in a multi-user environment.

What about this?
Instead of appending your record first, open your form to a new record
like this:
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Then just fill in the data as your query would do, e.g.
Forms!frmFaxLog!WorkOrderID=Me.WorkOrderID
 
Hmmm ...
Not so easy, that way -
especially if you are operating in a multi-user environment.

What about this?
Instead of appending your record first, open your form to a new record
like this:
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Then just fill in the data as your query would do, e.g.
Forms!frmFaxLog!WorkOrderID=Me.WorkOrderID
Click to expand...

Thanks, that's very helpful! My partial solution was the following (a seek
to last record):

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryFaxLogAppendWorkOrderRealtor"
cmd.Execute longRecs, Array(Me.WorkOrderID)
stDocName = "frmFaxLog"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acLast

but as you have noted, this can fail to open the correct record in a
multi-user environment.

Thanks again for your assistance.

Best,
Christopher
 
DoCmd.OpenForm stDocName, datamode:=acFormAdd
Forms!frmFaxLog!WorkOrderID=Me.WorkOrderID
Click to expand...

This solution is exactly what I needed. However, the acFormAdd property is
apparently persistent. That is, later when I open frmFaxLog to review all
the records, it is still in acFormAdd mode. How and where should I reset
the acFormAdd property? Should I change it back each time the form is
closed?

I'm assuming I can use the same frmFaxLog to a) append a record to the log
when a button is clicked, and b) to review all the records. Let me know if
that is a poor design decision.

Best,
Christopher
 
Christopher said:
This solution is exactly what I needed. However, the acFormAdd
property is apparently persistent. That is, later when I open
frmFaxLog to review all the records, it is still in acFormAdd mode. How and
where should I reset the acFormAdd property? Should I change
it back each time the form is closed?

I'm assuming I can use the same frmFaxLog to a) append a record to
the log when a button is clicked, and b) to review all the records. Let me
know if that is a poor design decision.
Click to expand...

The argument does not persist. You must have DataEntry set to Yes in the form's
design view. Just set that to No.
 
The argument does not persist. You must have DataEntry set to Yes in the
form's design view. Just set that to No.
Click to expand...

Hmm, not sure how that field got change, but many thanks. That got it!!!

Best,
Christopher
 
Back
Top