Passing Arguments from OpenForm Command to an Unbound Textbox (1 Viewer)

brucker4

New member
Local time
Today, 02:52
Joined
Mar 2, 2020
Messages
4
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:

HouseholdsT (contains Addresses)GuestsT (contains Guest names & contact info)AppsT (contains Program Application Information)
HouseholdID_PKGuestsID_PKAppID_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:

June7

AWF VIP
Local time
Yesterday, 22:52
Joined
Mar 9, 2014
Messages
5,399
Doesn't matter if control is bound or not as long as form is open. Why the full path reference instead of just Me.textboxname? Or reference the OpenArgs property instead.

Opening form has to do something with the passed value. Should open form to a new record. Then code in Open or Load event:
Code:
If Me.NewRecord Then
    Me.GuestID_FK = Split(Me.OpenArgs, "|")(0)
    Me.HouseholdID_FK = Split(Me.OpenArgs, "|")(1)
End If
Please post code between CODE tags to retain indentation and readability.
 
Last edited:

brucker4

New member
Local time
Today, 02:52
Joined
Mar 2, 2020
Messages
4
Why do you think argument is not passed?

Opening form has to do something with the passed value. Should open form to a new record. Then code in Open event:

If Me.NewRecord Then
Me.GuestID_FK = Split(Me.OpenArgs, "|")(0)
Me.HouseholdID_FK = Split(Me.OpenArgs, "|")(1)
End If

I was under the impression it wasn't passed because when I attempt to open the form, I get "Run-time error 13 Type Mismatch" & when I click debug, it is showing me

Code:
Private Sub Form_Load()

Dim GuestIDFK As Integer
Dim HouseholdIDFK As Integer

If Not IsNull(Me.OpenArgs) Then
    GuestIDFK = Left(OpenArgs, InStr(OpenArgs, "|") - 1)           <--------------this line is highlighted.
    HouseholdIDFK = Mid(OpenArgs, InStr(OpenArgs, "|") + 1) 

    [Forms]![Recertification Form]![GuestID_FK] = GuestIDFK
    [Forms]![Recertification Form]![HouseholdID_FK] = HouseholdIDFK
End If

End Sub

When I hover over the highlighted line, it's saying that there is nothing on the left side of the "|" therefore GuestIDFK=0

Is my code in the wrong place?
 
Last edited:

June7

AWF VIP
Local time
Yesterday, 22:52
Joined
Mar 9, 2014
Messages
5,399
Edited my previous post, apparently after you read it. Might review again.

If you want to provide db for analysis, follow instructions at bottom of my post.
 

brucker4

New member
Local time
Today, 02:52
Joined
Mar 2, 2020
Messages
4
Doesn't matter if control is bound or not as long as form is open. Why the full path reference instead of just Me.textboxname? Or reference the OpenArgs property instead.

Honestly, I've been tinkering with this so much trying to understand what I have wrong, that I probably entered the full path reference trying to see if that was my issue, and just left it there tbh. I guess I don't entirely understand the "Me" keyword, and am always wondering if that's my problem.

I've attached a sample version of what I'm working with..............I'm a novice. Please be nice lol.
 

Attachments

  • SampleDB.zip
    379.6 KB · Views: 348

June7

AWF VIP
Local time
Yesterday, 22:52
Joined
Mar 9, 2014
Messages
5,399
There is no code behind AddressChangeSF to grab OpenArgs and populate textbox.

Form is opening to new record, not to existing record. Do you want to edit existing address record?
Form is bound to HouseholdsT, not GuestsT.
HouseholdsT does not have GuestID_FK field which is the field referenced for filter criteria.
Exactly which address do you want to edit?

Did you copy/paste some code from a text file or web? Code has 'smart' quotes - they are tilted - Access does not like them. Need to replace with vertical quotes. The question marks are where you will find these bad quotes. Forum is not letting me paste the actual character.
Me.AddressChangeSearchBar.Value = ??
Me.Filter = ??

Suggest you set SearchBar to Null instead of empty string. It is not necessary to type .Value because Value is default property for data controls.
 

brucker4

New member
Local time
Today, 02:52
Joined
Mar 2, 2020
Messages
4
Wow! Thanks for all the amazing feedback.

This makes way more sense now. I thought I only needed code under the OnLoad event when I was parsing two arguments from the open arguments. I know absolutely nothing about programming/coding, so all the codes I've used have been copy/pasted from the web or were written very carefully in the notepad before I pasted it in myself.

As far as editing the address: So my biggest issue with this recertification process has to do with the address. What I would like, is for the user to be able to open the AddressChangeSF & search the street name. If the address is already in the system, I would like for the user to be able to click the "Select" button to the right of the address to open the [Recertification Form] and pass the GuestID_FK & the HouseholdID_PK open arguments through. If the address is not in the system, I would like for them to be able to add the address as a new record, then click the "Select" button to the right of the address to open the [Recertification Form] and pass the GuestID_FK and the new HouseholdID_PK open arguments through.

Am I on the right path?
 

June7

AWF VIP
Local time
Yesterday, 22:52
Joined
Mar 9, 2014
Messages
5,399
You are probably not wrong about using Load event to parse OpenArgs string. I think Open or Load event would work - edited my previous post.

Normally, a form is used to enter/edit data for a single table. Which table should Recertification form edit - Pantry? Can include related lookup tables to display their info but do not allow edit of their data. Set controls as Locked Yes and TabStop No.

Use form/subform arrangement if you want to edit multiple related tables. I don't understand AddressChangeF setup.

I am not clear on the address change process. Since guest has their own address record, why don't you just open to that record and edit? As I said, opening AddressChangeSF filtered by GuestID_FK makes no sense because that field is not in RecordSource.
 

Users who are viewing this thread

Top Bottom