Text box value as parameter in a query

[GillesScouvart via AccessMonster.com]

Hi all,

I have a form where the user can input a string in a textbox as the
description of a new record to be created (via append query). Then I need to
have other queries run using the PK on this new record.
I'm trying to get this PK by using:
SELECT TOP 1 SetID
FROM Sets
WHERE (((Sets.Description)=[Forms]![myForm].[txtSet])) ORDER BY SetID
DESC;
but it appears that the [Forms]![myFom].[txtSet] is null.

However, executing the following code in immediate window
debug.print([Forms]![F1 BOM tool]![txtSet])
gives me the right string ("this is a test").

And hardcoding the string also gives the right result:
SELECT TOP 1 SetID
FROM Sets
WHERE (((Sets.Description)="this is a test") ORDER BY SetID DESC;

Any idea on what may cause this problem (differences SQL vs VBA notations?
unsynchronized updates?)?

Thanks!

 

[RoyVidar]

Hi all,

I have a form where the user can input a string in a textbox as the
description of a new record to be created (via append query). Then I
need to have other queries run using the PK on this new record.
I'm trying to get this PK by using:
SELECT TOP 1 SetID
FROM Sets
WHERE (((Sets.Description)=[Forms]![myForm].[txtSet])) ORDER BY
SetID DESC;
but it appears that the [Forms]![myFom].[txtSet] is null.

However, executing the following code in immediate window
debug.print([Forms]![F1 BOM tool]![txtSet])
gives me the right string ("this is a test").

And hardcoding the string also gives the right result:
SELECT TOP 1 SetID
FROM Sets
WHERE (((Sets.Description)="this is a test") ORDER BY SetID DESC;

Any idea on what may cause this problem (differences SQL vs VBA
notations? unsynchronized updates?)?

Thanks!

What you have done, is putting the reference name into the string, not
the value. To do that, you'll need to concatenate it into the string

....WHERE Sets.Description = '" & [Forms]![myForm].[txtSet] & "'
Order...

Should it be possible that the string contains single quotes or other
special characters, try

....WHERE Sets.Description = """ & [Forms]![myForm].[txtSet] & """
Order...

replacing "myForm" with the name of the actual form.

 

[GillesScouvart via AccessMonster.com]

Thanks for the reply.
However, this SQL stands in a *query*, not in code, so there is no "string"
to concatenate.

Hi all,

[quoted text clipped - 21 lines]

Thanks!

What you have done, is putting the reference name into the string, not
the value. To do that, you'll need to concatenate it into the string

...WHERE Sets.Description = '" & [Forms]![myForm].[txtSet] & "'
Order...

Should it be possible that the string contains single quotes or other
special characters, try

...WHERE Sets.Description = """ & [Forms]![myForm].[txtSet] & """
Order...

replacing "myForm" with the name of the actual form.

 

[RoyVidar]

Thanks for the reply.
However, this SQL stands in a *query*, not in code, so there is no
"string" to concatenate.

Hi all,

[quoted text clipped - 21 lines]

Thanks!

What you have done, is putting the reference name into the string,
not the value. To do that, you'll need to concatenate it into the
string

...WHERE Sets.Description = '" & [Forms]![myForm].[txtSet] & "'
Order...

Should it be possible that the string contains single quotes or
other special characters, try

...WHERE Sets.Description = """ & [Forms]![myForm].[txtSet] & """
Order...

replacing "myForm" with the name of the actual form.

Then I read completely wrong - sorry

Sometimes, perhaps depending on how queries are executed, it seems
there's a race condition, or whatever to call it, when the first query
isn't finished executing when you run the next, so the result isn't
available. I guess that might could be the issue you experience (though
I'm not sure)

If this is what is causing this, then there are probably more than one
approach. If the PK is an autonumber, I will usually try to execute
both the insert and the retrieval of the new PK on the same (ADO)
connection. DAO is favoured in these groups, so you will probably also
receive such advice later too.

Say you have an append query called qryMyAppend, with one text
parameter, you should be able to do something along the lines of the
following (air code)

dim rs as adodb.recordset
with currentproject.connection ' or your ADO connection
.qryMyAppend Forms!myForm!txtSet
set rs = .execute("select @@identity",,adcmdtext)
debug.print rs.fields(0).value ' last autonumber
end with

 

[GillesScouvart via AccessMonster.com]

Thank you for your help RoyVidar, finally I think I've figured out what was
happening.
I transformed the subquery I used to retrieve the PK into a separate query,
and use this one into the other queries, and now it works.

Thanks for the reply.
However, this SQL stands in a *query*, not in code, so there is no

[quoted text clipped - 22 lines]
Then I read completely wrong - sorry

Sometimes, perhaps depending on how queries are executed, it seems
there's a race condition, or whatever to call it, when the first query
isn't finished executing when you run the next, so the result isn't
available. I guess that might could be the issue you experience (though
I'm not sure)

If this is what is causing this, then there are probably more than one
approach. If the PK is an autonumber, I will usually try to execute
both the insert and the retrieval of the new PK on the same (ADO)
connection. DAO is favoured in these groups, so you will probably also
receive such advice later too.

Say you have an append query called qryMyAppend, with one text
parameter, you should be able to do something along the lines of the
following (air code)

dim rs as adodb.recordset
with currentproject.connection ' or your ADO connection
.qryMyAppend Forms!myForm!txtSet
set rs = .execute("select @@identity",,adcmdtext)
debug.print rs.fields(0).value ' last autonumber
end with