Hello! I am an extreme novice at Access and VBA, but I have been tasked with this database project at work. I have been trucking along trying to troubleshoot as I go, and I have finally found an issue that I haven't been able to workaround. Any help you all can provide to me would be a godsend.
I have been tasked with creating a database to store the application details for a program I manage. The program participants (referred to as guests) are required to reapply every six months in order to remain "active" to receive services. I need to be able to keep a copy of each guest application in the database in order to make my annual reports at the end of the year, so I have developed three tables:
I created a query (ProgramAppsQ) based on these three tables, and I created a continuous form (SearchF) that uses ProgramAppsQ as a record source. I have applied a dynamic filter onto this form in order to use it to search. I have created a button on the continuous form (RecertButton) that triggers the following event procedure:
It essentially asks the user if they want to proceed with recertifying a guest and asks them to verify the address. When the user chooses "yes", it opens the [Recertification Form] to add a new record with the foreign keys passed along in the open arguments. I have been able to get this to work without issue. My main issue is when the user chooses "no".
What I want to happen is that when the user chooses no, another continuous form (AddressChangeSF) bound to the HouseholdsT opens with the GuestID_FK passed in an open argument to an unbound textbox of the same name. Once an address has been selected, the user can click the (SaveAddressButton) which will trigger the following event procedure:
This procedure is supposed to open the [Recertification Form], passing on the GuestID and HouseholdID foreign keys to a new record through the open arguments.
I notice that the argument is not being passed from SearchF to [AddressChangeSF], & thusly is not available to be passed on to the Recertification Form? What am I doing wrong? Is it because the textbox on AddressChangeSF is unbound?
I appreciate any insight as I have to complete this before the weekend is out.
Thanks so much in advance,
B
I have been tasked with creating a database to store the application details for a program I manage. The program participants (referred to as guests) are required to reapply every six months in order to remain "active" to receive services. I need to be able to keep a copy of each guest application in the database in order to make my annual reports at the end of the year, so I have developed three tables:
HouseholdsT (contains Addresses) | GuestsT (contains Guest names & contact info) | AppsT (contains Program Application Information) |
HouseholdID_PK | GuestsID_PK | AppID_PK (Indexed) (Composite Key) |
HouseholdID_FK (Composite Key) | ||
GuestID_FK (Composite Key) |
I created a query (ProgramAppsQ) based on these three tables, and I created a continuous form (SearchF) that uses ProgramAppsQ as a record source. I have applied a dynamic filter onto this form in order to use it to search. I have created a button on the continuous form (RecertButton) that triggers the following event procedure:
Code:
Private Sub RecertButton_Click()
Dim Msg, Style, Title, Response
Msg = Me.GuestName & Chr(13) & Chr(10) & Me.HouseholdAddress & Chr(13) & Chr(10) & Chr(13) & Chr(10)
Msg = Msg & "Is their address still the same?"
Style = vbYesNoCancel + vbDefaultButton3
Title = "You are attempting to recertify the following guest:"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then 'User chose yes'
DoCmd.OpenForm "Recertification Form", , , , , , Me.GuestID_PK & "|" & Me.HouseholdID_FK
ElseIf Response = vbNo Then 'User chose no'
DoCmd.OpenForm "AddressChangeSF", , , "Forms![AddressChangeSF]![GuestID_FK] = " & Me.GuestID_PK
Else 'User chose Cancel'
End If
End Sub
It essentially asks the user if they want to proceed with recertifying a guest and asks them to verify the address. When the user chooses "yes", it opens the [Recertification Form] to add a new record with the foreign keys passed along in the open arguments. I have been able to get this to work without issue. My main issue is when the user chooses "no".
What I want to happen is that when the user chooses no, another continuous form (AddressChangeSF) bound to the HouseholdsT opens with the GuestID_FK passed in an open argument to an unbound textbox of the same name. Once an address has been selected, the user can click the (SaveAddressButton) which will trigger the following event procedure:
Code:
Private Sub SaveAddressButton_Click()
DoCmd.OpenForm "Recertification Form", , , , , , [Forms]![AddressChangeSF]![GuestID_FK] & "|" & Me.HouseholdID_PK
End Sub
This procedure is supposed to open the [Recertification Form], passing on the GuestID and HouseholdID foreign keys to a new record through the open arguments.
I notice that the argument is not being passed from SearchF to [AddressChangeSF], & thusly is not available to be passed on to the Recertification Form? What am I doing wrong? Is it because the textbox on AddressChangeSF is unbound?
I appreciate any insight as I have to complete this before the weekend is out.
Thanks so much in advance,
B
Last edited: