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

[AccessIdiot]

Perfect! That works a charm. One last question if I may (although feel free to chime in if you figure out why I'm getting the stupid error message).

How can I clear the textbox with the survey number when I advance to a new record? Right now if I type in '3' it stays there for the new record.

Thanks for all your help!

[Rabbit]

Clear the field from the On Change event of the form.

Private Sub Form_Change()
Me.[Textbox Name] = ""
End Sub

[Rabbit]

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?

Is the form bound to the table tbl_Staff or is it bound to a different table?

What tables are there and how are they related to each other?

You said you haven't gotten to that field on the form yet, what field are you talking about?

[AccessIdiot]

Clear the field from the On Change event of the form.

Private Sub Form_Change()
Me.[Textbox Name] = ""
End Sub

For some reason this doesn't work? Or it works but as soon as I try to leave the textfield to fill out the rest of the form it clears and doesn't enter the value in the table. I think it is conflicting with the OnLostFocus event.

That's okay though as the guy I'm working with on this project wants to remove the built in next/previous/first/last/new record buttons on the forms and build our own. So I'll just have it clear when I build the buttons.

Is the form bound to the table tbl_Staff or is it bound to a different table?

What tables are there and how are they related to each other?

You said you haven't gotten to that field on the form yet, what field are you talking about?

There are fields on the form that are bound to different tables. These are the controls on the form and the tables they are tied to:

- Survey_ID (Primary Key of Survey table)
- Survey_Num (unbound holds integer)
- Survey_Num_ID (bound to Survey table holds concatenated value, e.g. 'TR2')
- Waterbody (a combo box populated by the Waterbody table but bound to the Survey table by Waterbody_ID, a foreign key in the Survey table)
- Location (similar to Waterbody - a combo box populated by the Location table but bound to the Survey table by the Location_ID, a foregin key in the Survey table)
- Staff (same as above, combo box populated by Staff table and bound by Staff_ID to Survey table).

This is the order of the controls/fields on the form. So I enter a number in the Survey_Num textbox, all is well. I enter a Waterbody, all is well, I enter a Location and I get the error message that a related record is required in the Staff table? But I haven't had the chance to enter the Staff yet because it is at the bottom of the form.

[Rabbit]

Does this happen all the time and always because of the location combobox?

Is the location table related to the staff table in any way? Is there any code acting on the staff combobox?

With a foreign key, you can't have any values that do not exist in the primary key table.

[AccessIdiot]

Does this happen all the time and always because of the location combobox?

Is the location table related to the staff table in any way? Is there any code acting on the staff combobox?

With a foreign key, you can't have any values that do not exist in the primary key table.

Hey sorry, I stepped out for lunch.

Yes it happens all the time and always on the Location drop down. The only fields in the Location table are Location_ID and Location so no, not related to the Staff table at all. I have no idea why it keeps barking on the Staff table - that table isn't related to anything else either, it contains only Staff_ID, Staff_Initials, and Staff_Name.

The only code on the staff combo box is a NotInList event that pops open a Staff form to add a new staff member. Both the Waterbody and Location dropdowns also have a NotInList event but it doesn't open another form, just adds the value to the dropdown.

[Rabbit]

First, try compiling the code to see if there are any problems.

Then try commenting out the code one section at a time and testing the form again to see if you can trace down which code is causing the error.