pass variable - OpenArgs?

[AccessIdiot]

Hi there,

Extreme newbie here so hopefully this is an easy one.

I have a switchboard with three buttons. Each button will open the same form BUT I need to pass a string depending on which button is pressed. In other words, Button A, Button B, and Button C on the switchboard. Press Button A and 'A' gets passed to Form1. Press Button B and 'B' gets passed to Form1. Press Button C and 'C' gets passed to Form1.

I realize I can use radio buttons to do this too - is that easier or better than using buttons? I like buttons because they make it very obvious to the user what the need to do.

Now here is where it gets tricky. On Form1 there is a field with an integer. I'm not sure yet whether this field will be an autonumber or will be manually entered by the user.

This number and the string passed by the button from the switchboard will be concatenated and stored in the table that supplies the fields for Form1. So if the user presses Button C and then on Form1 the number is 3 the value that is actually stored in the table is 'C3'.

So here is what I'm thinking I have to do, though I don't know the code that goes into it.

1) use OpenArgs to pass the string ('A', 'B', or 'C') from each button on the switchboard. Store the string in a variable somewhere that Form1 can access.

2) In the AfterUpdate() property of the textbox on Form1 that holds the integer concatenate the integer with the string variable and insert it into the table.

Is this about right? Please feel free to offer suggestions, alternatives, and most important, the how where and what of coding this idea.

thanks!
melissa

[Rabbit]

Hi there,

Extreme newbie here so hopefully this is an easy one.

I have a switchboard with three buttons. Each button will open the same form BUT I need to pass a string depending on which button is pressed. In other words, Button A, Button B, and Button C on the switchboard. Press Button A and 'A' gets passed to Form1. Press Button B and 'B' gets passed to Form1. Press Button C and 'C' gets passed to Form1.

I realize I can use radio buttons to do this too - is that easier or better than using buttons? I like buttons because they make it very obvious to the user what the need to do.

Now here is where it gets tricky. On Form1 there is a field with an integer. I'm not sure yet whether this field will be an autonumber or will be manually entered by the user.

This number and the string passed by the button from the switchboard will be concatenated and stored in the table that supplies the fields for Form1. So if the user presses Button C and then on Form1 the number is 3 the value that is actually stored in the table is 'C3'.

So here is what I'm thinking I have to do, though I don't know the code that goes into it.

1) use OpenArgs to pass the string ('A', 'B', or 'C') from each button on the switchboard. Store the string in a variable somewhere that Form1 can access.

2) In the AfterUpdate() property of the textbox on Form1 that holds the integer concatenate the integer with the string variable and insert it into the table.

Is this about right? Please feel free to offer suggestions, alternatives, and most important, the how where and what of coding this idea.

thanks!
melissa

Yep, this seems about right. Just a few things though.

1) The OpenArgs is a variable that is created by the form when it opens so it's already stored in a place that Form1 can access: Me.OpenArgs

2) You can concatenate using the & operator. You can just have the field bound to a control on the form and let Access auto-update the tables so you don't have to go through the trouble of programming an update.

Let us know if you get stuck anywhere.

[AccessIdiot]

Yep, this seems about right. Just a few things though.

1) The OpenArgs is a variable that is created by the form when it opens so it's already stored in a place that Form1 can access: Me.OpenArgs

2) You can concatenate using the & operator. You can just have the field bound to a control on the form and let Access auto-update the tables so you don't have to go through the trouble of programming an update.

Let us know if you get stuck anywhere.

Wow sweet, I LOVE the quick replies on this forum!

1) Okay I'm going to need some help with the coding. When I create a button on the switchboard and tell it to open Form1 (which is called frm_Survey in this case) then Access creates this code:

Private Sub btn_StartTrawl_Click()
On Error GoTo Err_btn_StartTrawl_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_Survey"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_StartTrawl_Click:
    Exit Sub

Err_btn_StartTrawl_Click:
    MsgBox Err.Description
    Resume Exit_btn_StartTrawl_Click
    
End Sub

I think I have to specify the OpenArgs command somewhere in the DoCmd.OpenForm line right? But where and how do I use it? (I'm googling this as soon as I post this too).

But you say that after I get the variable passed I can simply use Me.OpenArgs in the concatenation string with the integer? In other words, fieldInTable = 'Me.OpenArgs' & 'integerOnForm' or something like that?

2) can you expand on this? when you say 'have the field bound to a control on the form' what do you mean? I will have a field on the form to hold the integer but I don't want that field bound to the table unless/until it's concatenated with the string passed. Does that make sense? So as soon as the user types in '3' then 'A3' gets stored in the table. Do I still bind the field that will hold '3' to the table?

sorry for dumb questions and thank you!
melissa :-)

[Rabbit]

Wow sweet, I LOVE the quick replies on this forum!

1) Okay I'm going to need some help with the coding. When I create a button on the switchboard and tell it to open Form1 (which is called frm_Survey in this case) then Access creates this code:

Private Sub btn_StartTrawl_Click()
On Error GoTo Err_btn_StartTrawl_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_Survey"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btn_StartTrawl_Click:
    Exit Sub

Err_btn_StartTrawl_Click:
    MsgBox Err.Description
    Resume Exit_btn_StartTrawl_Click
    
End Sub

I think I have to specify the OpenArgs command somewhere in the DoCmd.OpenForm line right? But where and how do I use it? (I'm googling this as soon as I post this too).

But you say that after I get the variable passed I can simply use Me.OpenArgs in the concatenation string with the integer? In other words, fieldInTable = 'Me.OpenArgs' & 'integerOnForm' or something like that?

2) can you expand on this? when you say 'have the field bound to a control on the form' what do you mean? I will have a field on the form to hold the integer but I don't want that field bound to the table unless/until it's concatenated with the string passed. Does that make sense? So as soon as the user types in '3' then 'A3' gets stored in the table. Do I still bind the field that will hold '3' to the table?

sorry for dumb questions and thank you!
melissa :-)

1) OpenArgs is the seventh parameter.

DoCmd.OpenForm ,,,,,, OpenArgs

The concatenation would be:

Me.[Textbox Name] = Me.OpenArgs & Me.integerOnForm

No need for quotes unless you want literal values.

2) You wouldn't have to bind the field storing 3 to the table unless it's being stored in the table. You'd bind the field that stores 'A3' to the field in the table. So that whatever you type in that text box is stored to that record in that field on that table.

When you bind a form to a table. Whatever is typed into the form is saved to the table. So then each field in the table has a matching control on the form that represents the field.

[AccessIdiot]

1) OpenArgs is the seventh parameter.

DoCmd.OpenForm ,,,,,, OpenArgs

The concatenation would be:

Me.[Textbox Name] = Me.OpenArgs & Me.integerOnForm

No need for quotes unless you want literal values.

2) You wouldn't have to bind the field storing 3 to the table unless it's being stored in the table. You'd bind the field that stores 'A3' to the field in the table. So that whatever you type in that text box is stored to that record in that field on that table.

When you bind a form to a table. Whatever is typed into the form is saved to the table. So then each field in the table has a matching control on the form that represents the field.

1) Okay, sorry for the confusion but where would I specify that the button is passing the text 'A'? Do I need DoCmd.OpenForm , , , , , , OpenArgs('A') or something like that?

2) See I don't technically have a field on the form that stores 'A3'. All I have is the value 'A' from the OpenArgs and the value '3' from the textbox on the form. I want to concatenate them and store that in the table. That's why I thought I'd need an AfterUpdate() function for the textbox.

Sorry if I am missing something really obvious here!

:-) melissa

[AccessIdiot]

Wait, I think I got the first one:

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "A"

Correct? Also, what does the stLinkCriteria refer to? It's something Access created when I told it to create a button.

[Rabbit]

Wait, I think I got the first one:

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "A"

Correct? Also, what does the stLinkCriteria refer to? It's something Access created when I told it to create a button.

That's correct.

stLinkCriteria is a variable that holds a string.
Because of where this variable is located, it becomes an argument that filters your form when it opens.

It's like the Where statement in SQL.

However, because it is blank, it ends up not filtering any records and is therefore unnecessary for your purposes.

[Rabbit]

1) Okay, sorry for the confusion but where would I specify that the button is passing the text 'A'? Do I need DoCmd.OpenForm , , , , , , OpenArgs('A') or something like that?

2) See I don't technically have a field on the form that stores 'A3'. All I have is the value 'A' from the OpenArgs and the value '3' from the textbox on the form. I want to concatenate them and store that in the table. That's why I thought I'd need an AfterUpdate() function for the textbox.

Sorry if I am missing something really obvious here!

:-) melissa

You're going to want to bind the form to the table and create a textbox to store that value. If you don't want the user to see it you can set the visibility to No.

Yes, you will put the code in the After Update event for the textbox storing the number but the code will assign a value to the textbox storing the concatenated string.

Private Sub Number_AfterUpdate()
Me.Concatenated = Me.OpenArgs & Me.Number
End Sub

[AccessIdiot]

Yeah, I didn't think I needed that stLinkCriteria since the value wasn't set to anything but I wasn't sure.

So I am experimenting (it's so fun when it actually does something and doesn't give me an error!) and I tried this for the AfterUpdate event on the textbox (which is called txt_SurveyNum).

Private Sub txt_SurveyNum_AfterUpdate()
Me.txt_SurveyNum = Me.OpenArgs & Me.txt_SurveyNum
End Sub

This works great! As soon as the user types a number into txt_SurveyNum and hits the enter key it changes the value from say '7' to 'TR7' which is just what I wanted.

If you DON'T hit the enter key and just move the mouse to the next field on the form the value remains '7'. So we tried moving the code to the LostFocus event for the textbox and now it seems to work no matter what. :-)

However if I type in a value and hit the enter key I get an annoying pop up from Access that "You cannot add or change a record because a related record is require in table 'tbl_Staff'". But I haven't gotten to that field on the form yet! It is something I will fill in but the txt_SurveyNum is first.

How can I avoid getting this error message?

[Rabbit]

Did you delete the code from the after update event?
The only reason I can think of for the error is because when you hit enter, both the lost focus and after update events trigger. And you end up with some sort of circular reference.

[AccessIdiot]

Yes I did get rid of it. It's like Access wants me to fill out all the fields that are related to other tables, which I'm going to do, but it freaks out and yells at me preemptively. :-)

I tried compacting and repairing the database and that worked but then it popped up again on another field on the same form. And now it is adding a second 'TR' label to my field, I guess because it has lost focus a second time.

Any ideas?

[Rabbit]

I'm unsure as to why the errors are popping up, maybe others will have some more insight.

But you can bypass errors resulting from the code by putting
On Error Resume Next
at the beginning of your code.

Yes, I realized that would happen. That's why you should seperate your number field from the concatenated field. That way it will only concatenate the number field with OpenArgs and populate a third field.

[Rabbit]

Private Sub txt_SurveyNum_LostFocus()
Me.txt_SomeOtherField = Me.OpenArgs & Me.txt_SurveyNum
End Sub

And you'll want to bind this txt_SomeOtherFi eld to whatever field you want to store it to in the table.

[AccessIdiot]

I'm unsure as to why the errors are popping up, maybe others will have some more insight.

But you can bypass errors resulting from the code by putting
On Error Resume Next
at the beginning of your code.

Yes, I realized that would happen. That's why you should seperate your number field from the concatenated field. That way it will only concatenate the number field with OpenArgs and populate a third field.

Yes you are right. I was just hoping to avoid an extra field in the database that had repeated information, e.g. '2' in many records instead of unique values like ''TR2', 'BS2', 'PL2', etc. I guess some things can't be avoided if you want the forms to work every time.

[Rabbit]

Yes you are right. I was just hoping to avoid an extra field in the database that had repeated information, e.g. '2' in many records instead of unique values like ''TR2', 'BS2', 'PL2', etc. I guess some things can't be avoided if you want the forms to work every time.

The solution to this is to just get rid of the number field from the table but have a number textbox on your form. That way you're only storing TR2, BS2 only and not both TR2, BS2 and 2, 2