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!
8 1753
I guess you'll have to pass the Cabin to the subform to filter just for the people needed....
Nic;o)
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?
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_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?
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)
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?
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: 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)
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?
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)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Josh C. |
last post by:
Hello everyone. I'm a bit of an Access newbie, so please bear with me.
Please go to http://www.dumoti.com/access/ to view the database - 536kb.
I'll go straight into examples:
In the form...
|
by: Alienz |
last post by:
I have a subform where I have a subform with 20 options to select
from. When I set the multiselect property to simple and select
multiple options, nothing is stored. I have another table with...
|
by: xmp333 |
last post by:
Hi,
I have a form that is designed as a data sheet view. Attached to this
is a subform with some VB code. When the user clicks on a row, the
subform should pop up and run the VB code which...
|
by: xmp333 |
last post by:
Hi,
I have a data sheet with a subform; each time the user clicks on the
"+" to show the subform, I want some code to run. I tried attaching
the code to a wide variety of events, but at best...
|
by: jballard |
last post by:
Hello,
I have a database set-up with a form and two subforms in it. I have one
of the subforms (replacement parts) set-up where you can pick part
numbers from a drop down box and also pick a...
|
by: kkrizl |
last post by:
I have a form that displays general information about an alarm permit
location. There's a subform that shows detailed information about
burglar alarms that have gone off at the location. When a...
|
by: sparks |
last post by:
I have a form/table with an autoid it is linked to a table/form with
and ID as a 1 to many.
Under this form/table I need another table with many records for each
on of the many in the previous...
|
by: Christina123 |
last post by:
Currently working with Microsoft Office 2000 and whatever version of Access came with that.
I am developing a database to track the comings and goings of shared tools.
Everything works...
|
by: mgstlucia |
last post by:
I have a Form (frmsOrders) with a subform (frmOrdersDetails Subform). I am trying to automatically fill in the Item Description, Unit and Price when choosing the SKU# from a combobox. I have this...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |