By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,311 Members | 1,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,311 IT Pros & Developers. It's quick & easy.

getting info from one subform to another

P: 111
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.

Oct 31 '07 #1
Share this Question
Share on Google+
8 Replies

Expert 2.5K+
P: 3,072
I guess you'll have to pass the Cabin to the subform to filter just for the people needed....

Oct 31 '07 #2

P: 111
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:

Expand|Select|Wrap|Line Numbers
  1. pax1 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
  2.     DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
  3.     pax2 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
  4.     DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
  5.     pax3 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
  6.     DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
  7.     pax4 = Forms![frmPaxInfo2].tblPaxInfo_subform.Form!FirstName & " " & Forms![frmPaxInfo2].tblPaxInfo_subform.Form!LastName
  8.     DoCmd.GoToRecord acDataForm, "tblPaxInfo subform", acNext
  9.     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?
Nov 1 '07 #3

P: 111
OK this is where im at:

Expand|Select|Wrap|Line Numbers
  1. Private Sub PaymentButton_Click()
  2. On Error GoTo Err_PaymentButton_Click
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.     Dim pax1 As String
  7.     Dim pax2 As String
  8.     Dim pax3 As String
  9.     Dim pax4 As String
  10.     Dim pax5 As String
  11.     Dim rst As ADODB.Recordset
  13.     Set rst = New ADODB.Recordset
  14.     rst.Open "tblPaxInfo", CurrentProject.Connection
  16.     rst.Filter = "[GroupNumber]= '" & Me![GroupNumber] & "' AND [ResNumber]= '" & Me![Individual Res #] & "'"
  18.     pax1 = rst("FirstName") & " " & rst("LastName")
  19.     rst.MoveNext
  20.     If rst.EOF Then
  21.         GoTo Continue
  22.     Else
  23.         pax2 = rst("FirstName") & " " & rst("LastName")
  24.     End If
  25.     rst.MoveNext
  27.     If rst.EOF Then
  28.         GoTo Continue
  29.     Else
  30.         pax3 = rst("FirstName") & " " & rst("LastName")
  31.     End If
  32.     rst.MoveNext
  34.     If rst.EOF Then
  35.         GoTo Continue
  36.     Else
  37.         pax4 = rst("FirstName") & " " & rst("LastName")
  38.     End If
  39.     rst.MoveNext
  41.     If rst.EOF Then
  42.         GoTo Continue
  43.     Else
  44.         pax5 = rst("FirstName") & " " & rst("LastName")
  45.     End If
  46. Continue:
  48.     stDocName = "frmPaymentsNEW"
  50.     stLinkCriteria = "[Individual Res #]=" & "'" & Me![Individual Res #] & "'"
  51.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  53.     Forms![frmPaymentsNEW]![GroupNumber] = Forms![frmPaxInfo2]![GroupNumber]
  54.     Forms![frmPaymentsNEW]![GroupName] = Forms![frmPaxInfo2]![GroupName]
  56.     Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax1
  57.     DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
  58.     Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax2
  59.     DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
  60.     Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax3
  61.     DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
  62.     Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax4
  63.     DoCmd.GoToRecord acDataForm, "tblPayments_subform", acNewRec
  64.     Forms![frmPaymentsNEW].tblPayments_subform.Form![PaxName] = pax5
  66. Exit_PaymentButton_Click:
  67.     Exit Sub
  69. Err_PaymentButton_Click:
  70.     MsgBox Err.Description
  71.     Resume Exit_PaymentButton_Click
  73. 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_subform" isn't open, but it is on the current form. How can i reference this subform to go to a new record on it?
Nov 1 '07 #4

Expert 2.5K+
P: 3,072
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 ?

Nov 1 '07 #5

P: 111
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_subform.

Can you help me from here?
Nov 1 '07 #6

Expert 2.5K+
P: 3,072
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 tblPassengerFlight.
The tblPayments is linked to the tblPassengerFlight 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:

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....

Nov 1 '07 #7

P: 111
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?
Nov 2 '07 #8

Expert 2.5K+
P: 3,072
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...

Nov 2 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.