By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,603 Members | 1,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,603 IT Pros & Developers. It's quick & easy.

Passing an ID number to acGoTo (newbie)

P: 6
Hi
I've never really worked with VBScript - I'm an old-fashioned HTML guy - but I thought I'd have a go because I wanted to do something REALLY SIMPLE in my Access 2000 database. This is a plea for help, days later, having trawled the Internet repeatedly and experimented as much as I dare looking for anything that will work.

I have a form called Communications (I now know it shoulda been frm_Communications!). In it there is a combo box called Abbreviation. This refers to the Row Source: qry_Union_Abbreviations to get a list of abbreviations, along with ID number and Full name, from a Table called Communications (yep, shoulda been tbl_Communications!).

The query concerned has one bound column, and the field ID is the first one, and is indexed and a primary key (in the form "Unions" which I'm wanting to call).

Anyway - the "simple task" I set myself was to put a little button beside this combo box which, when clicked on, would refer to the data in the Combo box and then open another form and go to the record whose value was that named in the Combo box.

Here's the code I have used for my On_Click Event Procedure:

Private Sub Union_more_Click()
intGlobalVariable = [Union Abbreviation]
DoCmd.OpenForm "Unions"
DoCmd.GoToRecord , , acGoTo, intGlobalVariable
End Sub

intGlobalVariable is defined as an Integer in a module called Module1. which also contains strGlobalVariable, defined as a string.

On click I get error number 2498 - "an expression you entered is the wrong data type for one of the arguments".

In debugging I see the acGoTo value is "4" (seems to be always 4, no matter which record I start from) while the intGlobalVariable is the text string in the combo box.

If I use strGlobalVariable it makes no difference.

I have to say I've tried everything I've been able to find in forums, and the experience has got me interested in VBScript, but I know for sure ain't gonna solve this one on my own. Any suggestions you can offer will help preserve my sanity.

PS If it's any help the following code DOES open the right form, and go to the right record:

Private Sub Union_more_Click()
DoCmd.OpenForm "Unions"
DoCmd.GoToRecord , , acGoTo, 7
End Sub

I think I just need to find a way to pass an ID number to acGoTo. But I'll let you be the judge of that.
May 24 '07 #1
Share this Question
Share on Google+
17 Replies

P: 6
Hi
I'm trying to set up a button which will go to a string value in another form. Too simple, you say?

I have a form called Communications. In it there is a combo box called Abbreviation. This refers to the Row Source: qry_Union_Abbreviations to extract a short name, along with the ID number, from a Table.

The query has one bound column, the field ID is the first one, and it is indexed and a primary key (in the form "Unions" which I'm wanting to call).

So the simple beginner's task I set myself was to put a little button beside this combo box which, when clicked, would refer to the data in the Combo box and then open up the other form and go to the record whose value was that named in the Combo box.

Here's the code I have used for my On_Click Event Procedure:

Private Sub Union_more_Click()
intGlobalVariable = [Union Abbreviation]
DoCmd.OpenForm "Unions"
DoCmd.GoToRecord , , acGoTo, intGlobalVariable
End Sub

On click I get error number 2498 - "an expression you entered is the wrong data type for one of the arguments".

In debugging I see the acGoTo value is "4" (seems to be always 4, no matter which record I start from) while the intGlobalVariable is the text string in the combo box.

If I use strGlobalVariable it makes no difference.

I've tried everything I've been able to find in forums, and the experience has been great, though frustrating. But I know for sure ain't gonna solve this one on my own. Any suggestions you can offer will help preserve my sanity.

PS If it's any help I'm using Access 2000 and the following code DOES open the right form, and go to the specified record (ie 7):

Private Sub Union_more_Click()
DoCmd.OpenForm "Unions"
DoCmd.GoToRecord , , acGoTo, 7
End Sub

I think I just need to find a way to pass an ID number to acGoTo. But I'll let you be the judge of that.
May 25 '07 #2

ADezii
Expert 5K+
P: 8,750
Hi
I'm trying to set up a button which will go to a string value in another form. Too simple, you say?

I have a form called Communications. In it there is a combo box called Abbreviation. This refers to the Row Source: qry_Union_Abbreviations to extract a short name, along with the ID number, from a Table.

The query has one bound column, the field ID is the first one, and it is indexed and a primary key (in the form "Unions" which I'm wanting to call).

So the simple beginner's task I set myself was to put a little button beside this combo box which, when clicked, would refer to the data in the Combo box and then open up the other form and go to the record whose value was that named in the Combo box.

Here's the code I have used for my On_Click Event Procedure:

Private Sub Union_more_Click()
intGlobalVariable = [Union Abbreviation]
DoCmd.OpenForm "Unions"
DoCmd.GoToRecord , , acGoTo, intGlobalVariable
End Sub

On click I get error number 2498 - "an expression you entered is the wrong data type for one of the arguments".

In debugging I see the acGoTo value is "4" (seems to be always 4, no matter which record I start from) while the intGlobalVariable is the text string in the combo box.

If I use strGlobalVariable it makes no difference.

I've tried everything I've been able to find in forums, and the experience has been great, though frustrating. But I know for sure ain't gonna solve this one on my own. Any suggestions you can offer will help preserve my sanity.

PS If it's any help I'm using Access 2000 and the following code DOES open the right form, and go to the specified record (ie 7):

Private Sub Union_more_Click()
DoCmd.OpenForm "Unions"
DoCmd.GoToRecord , , acGoTo, 7
End Sub

I think I just need to find a way to pass an ID number to acGoTo. But I'll let you be the judge of that.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Unions", acNormal, , "[ID] = " & Me![Abbreviation], acFormEdit, acWindowNormal
May 26 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try something like this instead.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Union_more_Click()
  2. Dim strCriteria As String
  3.    strCriteria = "[ID]=" & Me!Union_more 
  4.    DoCmd.OpenForm "Unions", , , strCriteria
  5.  
  6. End Sub
  7.  
May 26 '07 #4

P: 6
Thanks very much for this, but no luck. I'm perplexed by the result. The error message said that it could not find a field by the name of Abbreviation. I double checked the Control Source. No typos. There IS a field called Abbreviation in the form called Unions.

Does your suggestion rely on some underlying relationship or link between the two forms?
May 26 '07 #5

P: 6
Thanks for this, but I'm afraid it didn't work. On debugging the final strCriteria contains an empty value (if that's the right term). The field name [ID] is definitely right, so I don't get why. However it's also worth noting that the ID value in the Unions form will be different to the ID value in the form we start from (which is called Communications).

Wince. Any suggestions?
May 26 '07 #6

ADezii
Expert 5K+
P: 8,750
Thanks very much for this, but no luck. I'm perplexed by the result. The error message said that it could not find a field by the name of Abbreviation. I double checked the Control Source. No typos. There IS a field called Abbreviation in the form called Unions.

Does your suggestion rely on some underlying relationship or link between the two forms?
I based the code on:
In it there is a combo box called Abbreviation.
It seems as though your Combo Box may not be named Abbreviation at all. I am not referring to the 'Control Source' for the Combo Box but the 'Name' of the Combo Box. They probably are not the same. To obtain the Name of the Combo Box: Form Design View ==> right click on the Combo Box ==> Properties ==> All Tab ==> 1st item is Name. Substitute it for Abbreviation in the line of code.
May 26 '07 #7

missinglinq
Expert 2.5K+
P: 3,532
Please do not double post on the forum! It potentially wastes the time of the very people you're asking for help! The person responding on one post has no way of knowing what's been suggested to you on the other post, and whether or not the suggestion helped! You have mmccarthy and adezii both trying to help you, but neither is aware that the other has already made, essentially, the same suggestion, which isn't working!

If, after a reasonable time, you have not received help, you may move yur original post back up to the head of the queue, where it's likely to receive more attention, by simply posting a reply with the message "Bump!"
May 26 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Please do not double post on the forum! It potentially wastes the time of the very people you're asking for help! The person responding on one post has no way of knowing what's been suggested to you on the other post, and whether or not the suggestion helped! You have mmccarthy and adezii both trying to help you, but neither is aware that the other has already made, essentially, the same suggestion, which isn't working!

If, after a reasonable time, you have not received help, you may move yur original post back up to the head of the queue, where it's likely to receive more attention, by simply posting a reply with the message "Bump!"
I have merged both of these threads. Thanks Linq.

Mary
May 26 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for this, but I'm afraid it didn't work. On debugging the final strCriteria contains an empty value (if that's the right term). The field name [ID] is definitely right, so I don't get why. However it's also worth noting that the ID value in the Unions form will be different to the ID value in the form we start from (which is called Communications).

Wince. Any suggestions?
In the form Unions what is the control name of the textbox which has the ID field?

Are you trying to open a record to the ID which matches the selected record in the combobox?

What field is the combobox bound to?
May 26 '07 #10

ADezii
Expert 5K+
P: 8,750
Please do not double post on the forum! It potentially wastes the time of the very people you're asking for help! The person responding on one post has no way of knowing what's been suggested to you on the other post, and whether or not the suggestion helped! You have mmccarthy and adezii both trying to help you, but neither is aware that the other has already made, essentially, the same suggestion, which isn't working!

If, after a reasonable time, you have not received help, you may move yur original post back up to the head of the queue, where it's likely to receive more attention, by simply posting a reply with the message "Bump!"
Thanks for catching this. I had no idea that this was a Double Post.
May 26 '07 #11

NeoPa
Expert Mod 15k+
P: 31,770
I have merged both of these threads. Thanks Linq.

Mary
Nice one Linq.
I was just merging the two threads when the main one I was working from disappeared ;D

Peter,
Please review the thread so far, and put in a post explaining where you are and what, if anything, you are still having trouble with.
This may be easier to work with than the current (rather jumbled) situation.
This is (of course) exactly why we try to discourage the practice of double-posting in the first place. As a new user I'm sure you weren't aware of that so don't worry about it. Just try to clarify this situation if you would.
Seeing that you're from France, I understand that some of this may seem a little complicated. Let me know if you have any remaining difficulty.
May 26 '07 #12

P: 6
Sincere thanks folks, and apologies for the double posting. Yes, let's say its cos I'm French (as always, the truth is far too damned complicated).

To sum up: I've tried both solutions but neither works. However I can now confirm (with a loud D'Oh!) that the field in the first form is called [Union abbreviation]. I've tried lots of variations on your two suggestions, and the closest I can get is this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Union_more_Click()
  2.   DoCmd.OpenForm "Unions", acNormal, , "[Abbreviation] = " & Me![Union abbreviation], acFormEdit, acWindowNormal
  3.  
  4. End Sub
What happens here is interesting, and I'm guessing contains a clue as to where I'm going wrong. Clicking the button brings up a dialog box saying "Enter Parameter Value". Then there is the name that was in the Combobox above an empty text field.

If I enter this name into the text field and click Enter I go straight to the right record in the Unions form.

Soooooooo close!
May 26 '07 #13

NeoPa
Expert Mod 15k+
P: 31,770
If, as I suspect, your [Abbreviation] field is text then you need quotes (') around the value you're using.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Union_more_Click()
  2.   DoCmd.OpenForm "Unions", acNormal, , "[Abbreviation]='" & Me![Union abbreviation] & "'", acFormEdit, acWindowNormal
  3. End Sub
May 26 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Sincere thanks folks, and apologies for the double posting. Yes, let's say its cos I'm French (as always, the truth is far too damned complicated).

To sum up: I've tried both solutions but neither works. However I can now confirm (with a loud D'Oh!) that the field in the first form is called [Union abbreviation]. I've tried lots of variations on your two suggestions, and the closest I can get is this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Union_more_Click()
  2.   DoCmd.OpenForm "Unions", acNormal, , "[Abbreviation] = " & Me![Union abbreviation], acFormEdit, acWindowNormal
  3.  
  4. End Sub
What happens here is interesting, and I'm guessing contains a clue as to where I'm going wrong. Clicking the button brings up a dialog box saying "Enter Parameter Value". Then there is the name that was in the Combobox above an empty text field.

If I enter this name into the text field and click Enter I go straight to the right record in the Unions form.

Soooooooo close!
Peter it would help if you answered the questions I asked specifically as I'm not sure now which form you are talking about and I still don't know which column the combobox is bound to.

Based on your above code the textbox [Abbreviation] is on the Unions form and why are you matching it to a textbox called [Union abbreviation] on the current form instead of the combobox which based on your code is called [Union_more]?
May 26 '07 #15

P: 6
Thanks NeoPa. GOT IT !!!!!!!!!!

Thanks so much, all of you, for your help on this. And sorry MMcCarthy - I didn't understand your questions well enough to answer them. I'll now start deconstructing the successful script to see what on earth it's doing.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Union_more_Click()
  2.   DoCmd.OpenForm "Unions", acNormal, , "[Abbreviation]='" & Me![Union abbreviation] & "'", acFormEdit, acWindowNormal
  3. End Sub
  4.  
The biggest regret I have is that I won't be able to pass this favour on.... well, not in VBscripting anyway. I don't know where your politics are at, but we're a non-profit building a global unions database to help working people network across borders (per www.newunionism.net), so you've helped in a good cause. In fact this community is a great functional example of what we're trying to achieve.
May 27 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks NeoPa. GOT IT !!!!!!!!!!

Thanks so much, all of you, for your help on this. And sorry MMcCarthy - I didn't understand your questions well enough to answer them. I'll now start deconstructing the successful script to see what on earth it's doing.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Union_more_Click()
  2.   DoCmd.OpenForm "Unions", acNormal, , "[Abbreviation]='" & Me![Union abbreviation] & "'", acFormEdit, acWindowNormal
  3. End Sub
  4.  
The biggest regret I have is that I won't be able to pass this favour on.... well, not in VBscripting anyway. I don't know where your politics are at, but we're a non-profit building a global unions database to help working people network across borders (per www.newunionism.net), so you've helped in a good cause. In fact this community is a great functional example of what we're trying to achieve.
Thanks Peter that's fine. As long as you've got it working thats the main thing. I'm glad you like what we are doing with the forum.

Mary
May 27 '07 #17

NeoPa
Expert Mod 15k+
P: 31,770
Thanks NeoPa. GOT IT !!!!!!!!!!

Thanks so much, all of you, for your help on this. And sorry MMcCarthy - I didn't understand your questions well enough to answer them. I'll now start deconstructing the successful script to see what on earth it's doing.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Union_more_Click()
  2.   DoCmd.OpenForm "Unions", acNormal, , "[Abbreviation]='" & Me![Union abbreviation] & "'", acFormEdit, acWindowNormal
  3. End Sub
  4.  
The biggest regret I have is that I won't be able to pass this favour on.... well, not in VBscripting anyway. I don't know where your politics are at, but we're a non-profit building a global unions database to help working people network across borders (per www.newunionism.net), so you've helped in a good cause. In fact this community is a great functional example of what we're trying to achieve.
I'm glad you got that working Peter, and please ask for clarification on any items you need.
As to your web site, I've left the name in, but you'll notice the links are disabled. I (personally) have sympathy with your ideals, but I cannot leave actual links to political web sites in the forums. I hope you understand.
May 27 '07 #18

Post your reply

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