add new record based on selected record

[timbits35 via AccessMonster.com]

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_Command71_Click:
Exit Sub

Err_Command71_Click:
MsgBox Err.Description
Resume Exit_Command71_Click

End Sub

Thank you,
Liane

 

[Tom Wickerath]

Hi Liane,

It displays the data for the invoice, but not in datasheet view like I want.

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] & "'"


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

And I can't seem to add new records for the invoice I selected.

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


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

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_Command71_Click:
Exit Sub

Err_Command71_Click:
MsgBox Err.Description
Resume Exit_Command71_Click

End Sub

Thank you,
Liane