add new record based on selected record

add new record based on selected record

Post by timbits35 » Tue, 06 Jan 2009 12:30:30

I have a sales and invoicing database.

Sales table - PK SalesID, FK MemberID, field Invnum
Payments Table - PK PmtID, field Invnum
Sales Details Table - FK SalesID
Members Table - PK MemberID

I have a sales form with a combo box that allows me to search for an invoice
number and display the corresponding record. I also have a payments form that
does nothing yet. I want to after selecting the invoice I want, open the
payments form to the corresponding record and then display all the payments
so far for that invoice and then allow me to add new records if I choose. So
far I have added a command button using the wizard to open the payments form.
It displays the data for the invoice, but not in datasheet view like I want.
And I can't seem to add new records for the invoice I selected. I added a
button on the payments form to add a new payment, but it doesn't seem to
recognize that I want to add a payment to the current invoice. Please help
with the next steps.

CODE to open the payments form to the currently selected invoice.

Private Sub Command71_Click()
On Error GoTo Err_Command71_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmpayments"

stLinkCriteria = "[Invnum]=" & "'" & Me![InvNum] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit Sub

MsgBox Err.Description
Resume Exit_Command71_Click

End Sub

Thank you,

Message posted via

add new record based on selected record

Post by VG9tIFdpY2 » Tue, 06 Jan 2009 14:21:27

Hi Liane,

There is an optional View parameter that you can use to specify opening a
form in datasheet mode. Here is an example:

Option Compare Database
Option Explicit

Private Sub cmdOpenPaymentsForm_Click()
On Error GoTo ProcError

DoCmd.openForm "frmpayments", View:=acFormDS, _
WhereCondition:="[Invnum]=" & "'" & Me![InvNum] & "'"

Exit Sub
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdOpenPaymentsForm_Click..."
Resume ExitProc
End Sub

Can you add a new record if you open the frmpayments by itself?

Tom Wickerath
Microsoft Access MVP