469,306 Members | 1,987 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

pass variable - OpenArgs?

AccessIdiot
493 256MB
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
Mar 6 '07 #1
21 5686
Rabbit
12,516 Expert Mod 8TB
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.
Mar 6 '07 #2
AccessIdiot
493 256MB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_StartTrawl_Click()
  2. On Error GoTo Err_btn_StartTrawl_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frm_Survey"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9.  
  10. Exit_btn_StartTrawl_Click:
  11.     Exit Sub
  12.  
  13. Err_btn_StartTrawl_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_btn_StartTrawl_Click
  16.  
  17. 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 :-)
Mar 6 '07 #3
Rabbit
12,516 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_StartTrawl_Click()
  2. On Error GoTo Err_btn_StartTrawl_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frm_Survey"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9.  
  10. Exit_btn_StartTrawl_Click:
  11.     Exit Sub
  12.  
  13. Err_btn_StartTrawl_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_btn_StartTrawl_Click
  16.  
  17. 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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm ,,,,,, OpenArgs
The concatenation would be:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 6 '07 #4
AccessIdiot
493 256MB
1) OpenArgs is the seventh parameter.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm ,,,,,, OpenArgs
The concatenation would be:
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 6 '07 #5
AccessIdiot
493 256MB
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.
Mar 6 '07 #6
Rabbit
12,516 Expert Mod 8TB
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.
Mar 6 '07 #7
Rabbit
12,516 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Number_AfterUpdate()
  2. Me.Concatenated = Me.OpenArgs & Me.Number
  3. End Sub
Mar 6 '07 #8
AccessIdiot
493 256MB
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).

Expand|Select|Wrap|Line Numbers
  1. Private Sub txt_SurveyNum_AfterUpdate()
  2. Me.txt_SurveyNum = Me.OpenArgs & Me.txt_SurveyNum
  3. 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?
Mar 6 '07 #9
Rabbit
12,516 Expert Mod 8TB
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.
Mar 6 '07 #10
AccessIdiot
493 256MB
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?
Mar 6 '07 #11
Rabbit
12,516 Expert Mod 8TB
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.
Mar 6 '07 #12
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. Private Sub txt_SurveyNum_LostFocus()
  2. Me.txt_SomeOtherField = Me.OpenArgs & Me.txt_SurveyNum
  3. End Sub
And you'll want to bind this txt_SomeOtherField to whatever field you want to store it to in the table.
Mar 6 '07 #13
AccessIdiot
493 256MB
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.
Mar 6 '07 #14
Rabbit
12,516 Expert Mod 8TB
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
Mar 6 '07 #15
AccessIdiot
493 256MB
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!
Mar 6 '07 #16
Rabbit
12,516 Expert Mod 8TB
Clear the field from the On Change event of the form.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Change()
  2. Me.[Textbox Name] = ""
  3. End Sub
Mar 6 '07 #17
Rabbit
12,516 Expert Mod 8TB
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?
Mar 6 '07 #18
AccessIdiot
493 256MB
Clear the field from the On Change event of the form.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Change()
  2. Me.[Textbox Name] = ""
  3. 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.
Mar 6 '07 #19
Rabbit
12,516 Expert Mod 8TB
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.
Mar 6 '07 #20
AccessIdiot
493 256MB
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.
Mar 6 '07 #21
Rabbit
12,516 Expert Mod 8TB
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.
Mar 7 '07 #22

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Don Seckler | last post: by
4 posts views Thread by Barry Edmund Wright | last post: by
1 post views Thread by Ray Holtz | last post: by
9 posts views Thread by Henrootje | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.