getting info from one subform to another

[Neekos]

I have a form called frmPaxInfo, and on this form is a subform called subfrmPaxInfo. This subform (which is filtered by cabin) contains the passengers name along with all charges associated with each passenger. On the main form there is a button which opens another form for payments. On the payments form there is a subform that displays the passengers name, payments made, and the date the payment was made. The problem im having is that when i click on the payments button, i want the passengers names from the subform on frmPaxInfo to autopopulate into the subform on the payments form. I tried using a DLookup, but that only works for one record, and there can be up to 5 passengers (each having a different record). Any ideas? Please let me know if you need more info.

Thanks!

[nico5038]

I guess you'll have to pass the Cabin to the subform to filter just for the people needed....

Nic;o)

[Neekos]

That wouldn't work in this situation, as the Payments subform is based off a different table, which would be basically data entry. The user would add the payments as they went along - so there is nothing to filter.

Since there is only 5 possible passengers, i tried to cycle through the subform and assign them to variables as so:

pax1 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
    DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
    pax2 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
    DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
    pax3 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
    DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
    pax4 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
    DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
    pax5 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName

but it tells me that "tblPaxInfo subform" isn't open (it is - on the current form). Is it not possible to reference subforms using this method?

[Neekos]

OK this is where im at:

Private Sub PaymentButton_Click()
On Error GoTo Err_PaymentButton_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim pax1 As String
    Dim pax2 As String
    Dim pax3 As String
    Dim pax4 As String
    Dim pax5 As String
    Dim rst As ADODB.Recordset
 
    Set rst = New ADODB.Recordset
    rst.Open "tblPaxInfo", CurrentProject.Connection
 
    rst.Filter = "[GroupNumber]= '" & Me![GroupNumber] & "' AND [ResNumber]= '" & Me![Individual Res #] & "'"
 
    pax1 = rst("FirstName") & " " & rst("LastName")
    rst.MoveNext
    If rst.EOF Then
        GoTo Continue
    Else
        pax2 = rst("FirstName") & " " & rst("LastName")
    End If
    rst.MoveNext
 
    If rst.EOF Then
        GoTo Continue
    Else
        pax3 = rst("FirstName") & " " & rst("LastName")
    End If
    rst.MoveNext
 
    If rst.EOF Then
        GoTo Continue
    Else
        pax4 = rst("FirstName") & " " & rst("LastName")
    End If
    rst.MoveNext
 
    If rst.EOF Then
        GoTo Continue
    Else
        pax5 = rst("FirstName") & " " & rst("LastName")
    End If
Continue:
 
    stDocName = "frmPaymentsNEW"
 
    stLinkCriteria = "[Individual Res #]=" & "'" & Me![Individual Res #] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
    Forms![frmPaymentsNEW]![GroupNumber] = Forms![frmPaxInfo2]![GroupNumber]
    Forms![frmPaymentsNEW]![GroupName] = Forms![frmPaxInfo2]![GroupName]
 
    Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax1
    DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
    Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax2
    DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
    Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax3
    DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
    Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax4
    DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
    Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax5
 
Exit_PaymentButton_Click:
    Exit Sub
 
Err_PaymentButton_Click:
    MsgBox Err.Description
    Resume Exit_PaymentButton_Click
 
End Sub

It gets to the bolded line and gets stuck. It puts the value for pax1 in, but I guess the DoCmd to go to a new record on the subform doesnt work (or at least the way i have it doesnt work). It tells me that "tblPayments_su bform" isn't open, but it is on the current form. How can i reference this subform to go to a new record on it?

[nico5038]

I'm really puzzled why you want to use this much code.
In general when you have a Cabin with a max of five passengers, there will be a record with CabinID and PassengerID.
Now you're having to record the payments, then the way would be to use this CabinID and PassengerID record to record the payments, thus an additional table with CabinID, PassengerID and PaymentID will be needed. This can be achieved by having a Mainform for the "CabinID and PassengerID" and a subform with the payment (or payments).
This Mainform for the "CabinID and PassengerID" can be filtered for just the CabinID, thus enabling the user to "walk through" all passengers of one Cabin.

One option is to insert a default payment row when the cabins are filled with passengers, but this isn't necessary. Using a Payment subform linked by the mainform by CabinID and PassengerID will cause Access to automatically fill these keyfields in a new record.

Getting the idea ?

Nic;o)

[Neekos]

im afraid im not getting it, and i apologize. I'm still fairly new to Access and don't totally understand it all yet. Sometimes i overthink things and make them harder than the really are, and thats probably what im doing here. Let's start from the beginning and ill give you the metadata for my tables and the concept of what im trying to accomplish.

The main form (frmPaxInfo) is based off a table (tblManifest) with the following metadata:

Field DataType
ID AutoNumber PK
GroupNumber Text
GroupName Text
IndividualRes# Text
CabinNumber Text
Category Text
CategoryNum Text
Dining Text
PhoneNum Text
Address Text
Address2 Text
City Text
State Text
ZIP Text
Email Text
Comments Text


This form has a subform based off tblPaxInfo with the following metadata:

Field DataType
ID AutoNum
GroupNumber Text PK
ResNumber Text
FirstName Text
LastName Text
DOB/Age Text
PastGuest# Text
Cznship Text
Grats Currency
Insurance Currency
CruiseRate Currency
PortCharges Currency
Taxes Currency
Air Currency
Other Currency


There is a button on the main form that opens up a new form called frmPayments. The metadata for this table (tblPayments) is:

Field DataType
GroupNumber Text PK
ResNumber Text
PaxName Text
Deposit1 Currency
Deposit1Date Date/Time
Deposit2 Currency
Deposit2Date Date/Time
TCAmount Currency
FinalPayment Currency
FinalDate Date/Time


I want the payments form to open so that all (up to 5) passengers will show on the same form, with columns for each deposit and such to be entered. tblPayments is empty at this point. When they click on the payment button, i would like the passengers names to autopopulate into the form the same way that they are on the tblPaxInfo_subf orm.

Can you help me from here?

[nico5038]

Hmm, I see the tables need some "normalization" .
For me I would have a tblPassengers, a tblFlights and a tblPayments.
The Passengers are scheduled in a flight in a relationtable tblPassengerFli ght.
The tblPayments is linked to the tblPassengerFli ght and will have to allow a passenger to have one or more payments for his/her flight.

You could read the article on normalization at:
http://www.thescripts.com/forum/thread585228.html

Keep in mind that a normalized database table structure will pay back in less coding and efforts down the road.
Just imagine in your tables that a customer needs three payments while you have two payment fields....

Nic;o)

[Neekos]

yes i have read about the normalization. The only reason i need 2 payment fields in that table is that it makes it easier to query by 1st, 2nd, or Final payments. There will never be any more than these, ever. If i only have one payment field there, it makes it impossible to query by the different types.

Can you try to help me with my payments form by using these tables?

[nico5038]

The First, Second and Final payment are no reason for having three currency fields.
When your customers have to pay in three times, then you're free to insert three rows in the payment table with the amount, a sequencenumber and the PaymentDate to indicate that the value has been payed.

With the present tables I see no need to have a tblPaxInfo and a tblPayments as both have the same PK field...

Nic;o)