Command Button Problem

[mforema]

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:

If ( ) Then ' If record exists, then open the query below.
    DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
Else ' If record does not exist, then show message below.
    MsgBox "No test(s) found for column " & [Column Name] & ".", vbInformation, "Sorry"
End If

What should go in the if statement?

Thanks!

[mforema]

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:

If ( ) Then ' If record exists, then open the query below.
    DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
Else ' If record does not exist, then show message below.
    MsgBox "No test(s) found for column " & [Column Name] & ".", vbInformation, "Sorry"
End If

What should go in the if statement?

Thanks!

I've tried this expression in the If statement:

[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!

[mforema]

I've tried this expression in the If statement:

[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???

[NeoPa]

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.

[mforema]

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

[JKing]

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

[mforema]

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:

Dim db As Database
Dim rstQuery As Recordset
 
Set db = CurrentDb
Set rstQuery = db.OpenRecordset("2 - Total Reflux Tests Query", dbOpenDynaset)
 
rstQuery.FindFirst ("[Column Name] = [Forms]![GeneralColumnSummary - view]![Control Name]")
 
If (rstQuery.NoMatch = False) Then
    DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
Else
    MsgBox "No tests found for column " & [Column Name] & ".", vbInformation, "Sorry"
End If

Do you see what I did wrong?
Thanks again.

[NeoPa]

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.

[NeoPa]

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:

Dim db As Database
Dim rstQuery As Recordset
 
Set db = CurrentDb
Set rstQuery = db.OpenRecordset("2 - Total Reflux Tests Query", dbOpenDynaset)
 
rstQuery.FindFirst ("[Column Name] = [Forms]![GeneralColumnSummary - view]![Control Name]")
 
If (rstQuery.NoMatch = False) Then
    DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
Else
    MsgBox "No tests found for column " & [Column Name] & ".", vbInformation, "Sorry"
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.

[NeoPa]

I've tried this expression in the If statement:

[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.

[mforema]

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:

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
FROM [2 - Total Reflux Tests]
WHERE ((([2 - Total Reflux Tests].Column)=[Forms]![GeneralColumnDetails - view]![Column Name]));

Thanks!

[mforema]

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!

[NeoPa]

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.

SELECT Plant,
       [Column],
       [Date],
       [Test Materials],
       [Time for Test],
       [Steam (pph)],
       [dP (mm Hg)],
       [TCP (mm Hg)],
       [# Calculated Stages],
       [Calculated HETP],
       [HETP Theoretical],
       [# Theoretical Stages],
       Efficiency
FROM [2 - Total Reflux Tests]
WHERE ([Column]=[Forms]![GeneralColumnDetails - view]![Column Name]);

[NeoPa]

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.

[mforema]

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:

Dim rstTable As Recordset
Set rstTable = CurrentDb.OpenRecordset("2 - Total Reflux Tests", dbOpenDynaset)
 
rstTable.FindFirst ("Column = '" & Forms![GeneralColumnDetails - view]![Column Name] & "'")
 
If (rstTable.NoMatch = 0) Then
    DoCmd.OpenQuery "2 - Total Reflux Tests Query", acViewNormal, acReadOnly
Else
    MsgBox "No tests found for column " & [Column Name] & ".", vbInformation, "Sorry"
End If

Thanks for your help. Your questions helped me to research the solution more effectively.

[NeoPa]

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 :)