Need command button on subform

[Scott B]

Greetings,

I have a form with a subform. The subform records guest stays based on the
guest in the main form. I need to be able to add data to another table that
records information on who to invoice if the guest is not paying. For
instance a company paying for the stay. I was hoping to have a command
button that would brng up the invoice info for the subform record. Is there
a way to get a command button on a subform or, if not, a way to get a
command button on the main form to do the same thing?

Thanks for the help.

Best regards,
Scott B

 

[Steve Schapel]

Scott,

You can put a command button on the main form, and then the code on its
Click event will refer to the current record on the subform. Probably
you would want the Record Selectors property of the subform set to Yes.
This can work ok, but it is easy to make a mistake with the wrong
subform record being selected. I normally prefer to put a neat little
command button in the Detail section of the subform. There is normally
no problem with doing it this way. The code on this button's Click
event will look something like this...
DoCmd.OpenForm "InvoiceInfo", , , "StayID=" & Me.StayID

 

[Scott B]

Steve,

I like the concept of the command button in the detail section of the
subform. I tried it, but it does not show up when I open the form. My
subform is in datasheet mode. Any thoughts?

Thanks,
Scott B

 

[Steve Schapel]

Scott,

Ah, datasheet. I have never used a subform in datasheet view, so this
possibility didn't occur to me. No, you can't put a command button onto
a datasheet. You will need to set up your form in continuous view. You
can format a continuous view form to look very much like a datasheet, if
you like.

 

[Scott B]

Steve,

I don't have a problem with a continuous form. I just converted it. It
works fine. I have never put a command button on a subform before and I am
having trouble with the linkage. The link between the from and the subform
is GuestID from the Stays table and ID (Primary Key) from the Guests table.
That form/subform works very well. But I cannot get the invoice
form/subform to populate data. It opens and does not give an error, but no
data. Here is the code behind the command button:
Private Sub cmdBillInvoice_Click()
On Error GoTo Err_cmdBilltoInvoice_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmBilltoInvoice"

stLinkCriteria = "[BillID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdBilltoInvoice_Click:
Exit Sub

Err_cmdBilltoInvoice_Click:
MsgBox Err.Description
Resume Exit_cmdBilltoInvoice_Click

End Sub


Here is the SQL for the form and the subform.

First the form:
SELECT [tblMainGuests].[ID], [tblMainGuests].[LastName],
[tblMainGuests].[FirstName], [tblMainGuests].[MI],
[tblMainGuests].[FirstName2], [tblMainGuests].[MI2],
[tblMainGuests].[LastName2], [tblMainGuests].[Address1],
[tblMainGuests].[Address2], [tblMainGuests].[City], [tblMainGuests].[State],
[tblMainGuests].[Province], [tblMainGuests].[PostalCode],
[tblMainGuests].[Country], [tblMainGuests].[AddressAlt1],
[tblMainGuests].[AddressAlt2], [tblMainGuests].[City2],
[tblMainGuests].[State2], [tblMainGuests].[Province2],
[tblMainGuests].[PostalCode2], [tblMainGuests].[Country2],
[tblMainGuests].[Company], [tblMainGuests].[Title],
[tblMainGuests].[HomePhone], [tblMainGuests].[HomePhone2],
[tblMainGuests].[WorkPhone], [tblMainGuests].[Fax],
[tblMainGuests].[CellPhone], [tblMainGuests].[E-Mail],
[tblMainGuests].[GuestMemo], [tblMainGuests].[ContactTypeID],
[tblMainGuests].[ReferralTypeID], [tblMainGuests].[PurchaseID],
[tblMainGuests].[NoMail], [tblMainGuests].[DateRecordCreated],
[tblMainGuests].[DateRecordUpdated], ([FirstName] & " "+[MI] & " " &
[LastName] & " and "+[FirstName2] & " "+[MI2] & " "+[LastName2]) AS [NAMES],
([Address1] & " " & [Address2]) AS ADDRESSES, ([City] & ", " &
(IIf(IsNull([State]),[Province],[State])) & " " & (IIf([Country]="USA","
",[Country])) & " " & [PostalCode]) AS STATEPROVINCE, ([FirstName] & "
"+[MI] & " " & [LastName]) AS NAME1, ([FirstName2] & " "+[MI2] & " " &
[LastName2]) AS NAME2, [tblMainGuests].[CardID]
FROM tblMainGuests
ORDER BY [tblMainGuests].[LastName], [tblMainGuests].[FirstName];

Now the subform:
SELECT [qryMainStays].[StayID], [qryMainStays].[Rate],
[qryMainStays].[Deposit], [qryMainStays].[ReserveDate],
[qryMainStays].[StayStart], [qryMainStays].[StayLength],
[qryMainStays].[StayMemo], [qryMainStays].[TaxExempt],
[qryMainStays].[GuestID], [qryMainStays].[GuestsperRoom],
[qryMainStays].[WeeklyStay], [qryMainStays].[RoomsID] FROM qryMainStays;

The linkage for the connection between the Stays table and the invoice table
is BillID for the Stays table and ID (Primary Key) for the invoice table.

Sorry about the length of this. I hope this is enough info to help figure
this out.

Best regards,
Scott B

 

[Steve Schapel]

Scott,

This code is running on a button called cmdBillInvoice which is on the
subform, right? Therefore, anything to do with the main form is not
relevant. So, Me![ID] would refer to the value of the [ID] field from
the subform. Well, it doesn't look like the subform has a [ID] field.
In any case, your description says that [BillID] is the field that links
the subform to the Invoice. Have I got it right? If so, you need to
include the BillID field on the subform, and then the code you need is
probably more like this...

Private Sub cmdBillInvoice_Click()
On Error GoTo Err_cmdBilltoInvoice_Click
DoCmd.OpenForm "frmBilltoInvoice", , , "[ID]=" & Me.BillID
Exit_cmdBilltoInvoice_Click:
Exit Sub
Err_cmdBilltoInvoice_Click:
MsgBox Err.Description
Resume Exit_cmdBilltoInvoice_Click
End Sub