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

Command Button Problem

P: 72
Hey Everybody,
I have code for a command button. The button is supposed to open a query with a general criteria of: Forms![Form Name]![Control Name]. The [Column Name] field of the query should match the [Control Name] in the Form. If a record does not exist in the query for the specified control name, I want a message box to display stating so. My code is below:

Expand|Select|Wrap|Line Numbers
  1. If ( ) Then ' If record exists, then open the query below.
  2.     DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
  3. Else ' If record does not exist, then show message below.
  4.     MsgBox "No test(s) found for column " & [Column Name] & ".", vbInformation, "Sorry"
  5. End If
What should go in the if statement?

Thanks!
Jun 13 '07 #1
Share this Question
Share on Google+
15 Replies


P: 72
Hey Everybody,
I have code for a command button. The button is supposed to open a query with a general criteria of: Forms![Form Name]![Control Name]. The [Column Name] field of the query should match the [Control Name] in the Form. If a record does not exist in the query for the specified control name, I want a message box to display stating so. My code is below:

Expand|Select|Wrap|Line Numbers
  1. If ( ) Then ' If record exists, then open the query below.
  2.     DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
  3. Else ' If record does not exist, then show message below.
  4.     MsgBox "No test(s) found for column " & [Column Name] & ".", vbInformation, "Sorry"
  5. End If
What should go in the if statement?

Thanks!
I've tried this expression in the If statement:
Expand|Select|Wrap|Line Numbers
  1. [2 - Total Reflux Tests]![Column Name] = Forms![GeneralColumnSummary - view]![Column Name]
But an error message popped up when I tried to run it:
"Run-time error '2465':
Microsoft Office Access can't find the field '|' referred to in your expression."

What can I do to fix it?
Thanks!
Jun 13 '07 #2

P: 72
I've tried this expression in the If statement:
Expand|Select|Wrap|Line Numbers
  1. [2 - Total Reflux Tests]![Column Name] = Forms![GeneralColumnSummary - view]![Column Name]
But an error message popped up when I tried to run it:
"Run-time error '2465':
Microsoft Office Access can't find the field '|' referred to in your expression."

What can I do to fix it?
Thanks!
Can someone please help me???
Jun 14 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
Please do not *BUMP* your thread before at least 24 hours have passed.
Our experts and members offer their time voluntarily and do not deserve to be treated with impatience by those who may benefit from their efforts.

MODERATOR.

I will look to answer this later but, like all the other experts, I don't appreciate your impatience. Having received a PM from you too, I'm not feeling too generous towards you at the moment. I'll look at this when I get home from work.
Jun 14 '07 #4

P: 72
Please do not *BUMP* your thread before at least 24 hours have passed.
Our experts and members offer their time voluntarily and do not deserve to be treated with impatience by those who may benefit from their efforts.

MODERATOR.

I will look to answer this later but, like all the other experts, I don't appreciate your impatience. Having received a PM from you too, I'm not feeling too generous towards you at the moment. I'll look at this when I get home from work.

To NeoPa and all other Moderators:

I am terribly sorry for coming accross as impatient. I am new to writing on Forums (I didn't even know what "BUMP" means), and thus I had no clue that what I was doing was considered impatient. I also did not know that sending you personal messages requesting help was frowned upon. So, to prevent anymore mistakes and annoyances, I will resign myself from this forum. I'm sorry for upsetting all of you. I really, truly am sorry.

~mforema
Jun 14 '07 #5

JKing
Expert 100+
P: 1,206
Adding this might work.
The logic behind this is to open the query and search for the first record that matches your control. If there is an existing record with the specified criteria Nomatch will return false. So, in theory this should provide you with the correct logic for your If statement.

Dim db as Database
Dim rstQuery As RecordSet

Set db = CurrentDb
Set rstQuery = db.OpenRecordSet ("qryName", dpOpenDynaset)

rstQuery.FindFirst ("[Column Name] = [Forms]![Form]![Control Name]")

If rstQuery.NoMatch = False Then
*the rest of your code here*

Hope this was helpful.
Jking
Jun 14 '07 #6

P: 72
Adding this might work.
The logic behind this is to open the query and search for the first record that matches your control. If there is an existing record with the specified criteria Nomatch will return false. So, in theory this should provide you with the correct logic for your If statement.

Dim db as Database
Dim rstQuery As RecordSet

Set db = CurrentDb
Set rstQuery = db.OpenRecordSet ("qryName", dpOpenDynaset)

rstQuery.FindFirst ("[Column Name] = [Forms]![Form]![Control Name]")

If rstQuery.NoMatch = False Then
*the rest of your code here*

Hope this was helpful.
Jking
Thanks JKing

I tried your code, but the an error message popped up saying "Too few parameters, expected:1." I don't know what that means. When trying to debug, the "Set rstQuery" line was highlighted. Here is my code so far:

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rstQuery As Recordset
  3.  
  4. Set db = CurrentDb
  5. Set rstQuery = db.OpenRecordset("2 - Total Reflux Tests Query", dbOpenDynaset)
  6.  
  7. rstQuery.FindFirst ("[Column Name] = [Forms]![GeneralColumnSummary - view]![Control Name]")
  8.  
  9. If (rstQuery.NoMatch = False) Then
  10.     DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
  11. Else
  12.     MsgBox "No tests found for column " & [Column Name] & ".", vbInformation, "Sorry"
  13. End If
Do you see what I did wrong?
Thanks again.
Jun 14 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
To NeoPa and all other Moderators:

I am terribly sorry for coming accross as impatient. I am new to writing on Forums (I didn't even know what "BUMP" means), and thus I had no clue that what I was doing was considered impatient. I also did not know that sending you personal messages requesting help was frowned upon. So, to prevent anymore mistakes and annoyances, I will resign myself from this forum. I'm sorry for upsetting all of you. I really, truly am sorry.

~mforema
We're really not all that thin-skinned, don't worry.
Simply, we have to deal with very large numbers of people of all sorts here, so replying in a way that is right for everyone is just not possible. I wanted to impart to you the understanding (which seems to have got across quite adequately) that such things are frowned on (and why). In my reply PM I posted a link so that you can better understand what we're dealing with here, and how members should be using this free resource that is TSDN.

I have no wish for you to leave the forums - that is not what we're all about.
Please enjoy the rest of your stay here.
Jun 14 '07 #8

NeoPa
Expert Mod 15k+
P: 31,494
Thanks JKing

I tried your code, but the an error message popped up saying "Too few parameters, expected:1." I don't know what that means. When trying to debug, the "Set rstQuery" line was highlighted. Here is my code so far:

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rstQuery As Recordset
  3.  
  4. Set db = CurrentDb
  5. Set rstQuery = db.OpenRecordset("2 - Total Reflux Tests Query", dbOpenDynaset)
  6.  
  7. rstQuery.FindFirst ("[Column Name] = [Forms]![GeneralColumnSummary - view]![Control Name]")
  8.  
  9. If (rstQuery.NoMatch = False) Then
  10.     DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
  11. Else
  12.     MsgBox "No tests found for column " & [Column Name] & ".", vbInformation, "Sorry"
  13. End If
Do you see what I did wrong?
Thanks again.
I've checked the line in question and I can't see what your problem could be.
Have you copied /pasted this code in. Sometimes it is very important that you do (Always do so as a general rule).
Is the query name absolutely accurate, and does the query run when opened from the database window?
Can you post the SQL of the query for us to look at please.
Jun 14 '07 #9

NeoPa
Expert Mod 15k+
P: 31,494
I've tried this expression in the If statement:
Expand|Select|Wrap|Line Numbers
  1. [2 - Total Reflux Tests]![Column Name] = Forms![GeneralColumnSummary - view]![Column Name]
But an error message popped up when I tried to run it:
"Run-time error '2465':
Microsoft Office Access can't find the field '|' referred to in your expression."

What can I do to fix it?
Thanks!
Going back to your earlier post, can you explain in simple English what the two sides of the = test refer to.
Again, nothing jumps out as to why the compiler would think there were an '|' in the equation anywhere.

PS. Lots of traffic since your last post. Best if you review all of it in sequence.
Jun 14 '07 #10

P: 72
I've checked the line in question and I can't see what your problem could be.
Have you copied /pasted this code in. Sometimes it is very important that you do (Always do so as a general rule).
Is the query name absolutely accurate, and does the query run when opened from the database window?
Can you post the SQL of the query for us to look at please.

I believe I copied/pasted the code. The name is also accurate, because I always copy and paste it into the code. The query also works everytime I open it from the database. The SQL for the query is as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT [2 - Total Reflux Tests].Plant, [2 - Total Reflux Tests].Column, [2 - Total Reflux Tests].Date, [2 - Total Reflux Tests].[Test Materials], [2 - Total Reflux Tests].[Time for Test], [2 - Total Reflux Tests].[Steam (pph)], [2 - Total Reflux Tests].[dP (mm Hg)], [2 - Total Reflux Tests].[TCP (mm Hg)], [2 - Total Reflux Tests].[# Calculated Stages], [2 - Total Reflux Tests].[Calculated HETP], [2 - Total Reflux Tests].[HETP Theoretical], [2 - Total Reflux Tests].[# Theoretical Stages], [2 - Total Reflux Tests].Efficiency
  2. FROM [2 - Total Reflux Tests]
  3. WHERE ((([2 - Total Reflux Tests].Column)=[Forms]![GeneralColumnDetails - view]![Column Name]));
Thanks!
Jun 18 '07 #11

P: 72
Going back to your earlier post, can you explain in simple English what the two sides of the = test refer to.
Again, nothing jumps out as to why the compiler would think there were an '|' in the equation anywhere.

PS. Lots of traffic since your last post. Best if you review all of it in sequence.

That 'equation' should mean that the [Column Name] in the table is the same as the current [Column Name] on the form. I hope that makes sense. I thought it would work in the IF...Else statement, b/c the 'Reflux Test' table only has data for certain 'Columns'; some 'columns' don't have reflux tests. So, I wanted the code to open the query for the current column on the form ONLY if there existed the reflux test data for that column. If I just open the query, and the current column on the form is not in the reflux table, then the query shows up empty. If the current column on the form is in the reflux table, then the query shows data for it. I hope this clarifies my problem.

Thank you for your help!
Jun 18 '07 #12

NeoPa
Expert Mod 15k+
P: 31,494
Before I go any further I need to repost an equivalent set of SQL for your query but which is legible. This has absolutely no effect on the query itself, just makes working on it a little easier.
Expand|Select|Wrap|Line Numbers
  1. SELECT Plant,
  2.        [Column],
  3.        [Date],
  4.        [Test Materials],
  5.        [Time for Test],
  6.        [Steam (pph)],
  7.        [dP (mm Hg)],
  8.        [TCP (mm Hg)],
  9.        [# Calculated Stages],
  10.        [Calculated HETP],
  11.        [HETP Theoretical],
  12.        [# Theoretical Stages],
  13.        Efficiency
  14. FROM [2 - Total Reflux Tests]
  15. WHERE ([Column]=[Forms]![GeneralColumnDetails - view]![Column Name]);
Jun 19 '07 #13

NeoPa
Expert Mod 15k+
P: 31,494
From this you will see that the column name in the table is actually [Column] rather than [Column Name].
Try changing your code (from post #7) to reflect this.
Jun 19 '07 #14

P: 72
From this you will see that the column name in the table is actually [Column] rather than [Column Name].
Try changing your code (from post #7) to reflect this.
Yes, very true. I changed it, and I still got an error message stating that Forms![GeneralColumnDetails - view]![Column Name] was not recognized as an acceptable field or expression.

So, I figured that I needed to take it out of the expression...and it worked! If that doesn't make any sense, I'm probably using the wrong jargon. Below is the code that finally worked:

Expand|Select|Wrap|Line Numbers
  1. Dim rstTable As Recordset
  2. Set rstTable = CurrentDb.OpenRecordset("2 - Total Reflux Tests", dbOpenDynaset)
  3.  
  4. rstTable.FindFirst ("Column = '" & Forms![GeneralColumnDetails - view]![Column Name] & "'")
  5.  
  6. If (rstTable.NoMatch = 0) Then
  7.     DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
  8. Else
  9.     MsgBox "No tests found for column " & [Column Name] & ".", vbInformation, "Sorry"
  10. End If
Thanks for your help. Your questions helped me to research the solution more effectively.
Jun 19 '07 #15

NeoPa
Expert Mod 15k+
P: 31,494
I'm pleased that helped you.
I would offer a little advice. Finding names for objects can be quite important when working with them. A long intricate name, which is also very similar to another long intricate name (for a different object) is likely to cause you confusion. Noticing when you're referring to the table and when the query is not too easy in your setup. Clarity helps to avoid confusion errors.
Anyway, good luck with your project :)
Jun 19 '07 #16

Post your reply

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