Solved SQL keeps failing on select

[Jallan!]

I have the following code. Just before the update statement below I call a public sub. I want to pass two work variables to this new public sub. One is text the other numeric. When I can get the SQL to work I then want to populate the item field in ![partnumber], the third field below form the SQL. I cannot get the SQL to work. I am working in Access.

Do While Not rstsrc.EOF

KeyField = rstsrc![entrysummarynumber] & rstsrc![entrysummarylinenumber] 'Sets unique value in new record using entry, line and ordinal

With rstupd
.AddNew
![esunique] = KeyField
![entrysummarynumber] = rstsrc![entrysummarynumber]
![PartNumber] = part
![EntryTypeCode] = rstsrc![EntryTypeCode]
![importerNumber] = rstsrc![importerNumber]
![PortofEntryCode] = rstsrc![importerNumber]
![EntryDate] = rstsrc![EntryDate]
![EntrySummaryDate] = rstsrc![EntrySummaryDate]
![EntrySummaryCreateDate] = rstsrc![EntrySummaryCreateDate]
![entrysummarylinenumber] = rstsrc![entrysummarylinenumber]

Work variables:
wrkentry = ![entrysummarynumber]
wrkentrynum = ![entrysummarylinenumber]
Call GetKNPn(wrkentry, wrkentrynum)

.Update

Public Sub GetKNPn(wrkentry, wrkentryline)

Set rstkndcr = db.OpenRecordset("SELECT [KNDCR].[Item] from KNDCR where KNDCR.[ENTRY] = wrkentry AND KNDCR.[ENTRYSUMMARYLINE] = wrkentryline")

 

[pbaldy]

I moved your thread out of the moderated code repository. You have to concatenate the variables into the string. I don't know which one is text, but whichever it is needs to be surround by text delimiters. Something like:

Set rstkndcr = db.OpenRecordset("SELECT [KNDCR].[Item] from KNDCR where KNDCR.[ENTRY] = " & wrkentry & " AND KNDCR.[ENTRYSUMMARYLINE] = '" & wrkentryline & "'")

Which treats the second variable as text.

 

[theDBguy]

Hi. Welcome to AWF!

It's good practice to assign the SQL string into a variable, so you can examine what the computer sees when it doesn't work.

Were you getting an error message?

 

[plog]

Var1="This is literal text inside Var1."
Var2="This is literal text inside Var2. If I just type Var1 inside of Var2 it just appears as text and does not put whats inside Var1 inside Var2."

Var3="I can, however, combine literal text like this, and variable data.  I do that by closing the quote marks, adding an ampersand and then typing my variable name. "  & Var1  & " I can even add more literal text like this after it, as long as I use an ampersand and go back into quotes."

Var2 does not contain the text from Var1 in it. Var3 does contain the text from Var1 in it.

 

[Jallan!]

Code suggestions worked. Now I get Error 2342 A runsql action requires an argument consisting of an sql statement. This is a select SQL that has been called from a public sub. In this first public sub I have created two variables and I am passing field values into those variables to use in the select SQL I am having issues with. These field values are from a source record I am processing. These variables I created are key fields that I need to use to return a unique record from a different table in the same Db, which is the reason for this SQL I am working on solving. In the immediate window:

SELECT KNDCR.Item from KNDCR where KNDCR.entry = 10105238703 AND KNDCR.EntrySummaryLine = 1; so, the revisions I made based on the help you provided seems to have worked. KNDCR.EntrySummaryLine is a Number value in both the source record and target record I am trying to retrieve. EntrySummaryLine, however is a text value.

Once I get this SQL to work, I want to use KNDCR.Item value as a value in the update that I am trying to achieve. Here is high level process:
Get source data
Create unique key field using concat for target record (unrelated to this SQL issue I am having)
Use a WITH to populate fields into target to write the record in the target from source record/fields
Pass values from source fields to this SQL I am having problems with to get the value in the field KNDCR.Item so I can use it in my .update statement.

If the value in the record I am trying to get (KNDCR.Item) is null, I will populate my target field value with "ERROR", but if there is an item number formatted as text I will move the item number to my field used in the .update and then write the record.

 

[plog]

Once I get this SQL to work, I want to use KNDCR.Item value as a value in the update that I am trying to achieve.

Knowing that, I think your method is incorrect.

You should just use a Dlookup to retrieve your value. No recordset, no SELECT query, just a Dlookup. You still need to get your criteria correct for it, but a DLookup is the way to retrieve a single value.

MS Access: DLookup Function

This MSAccess tutorial explains how to use the Access DLookup function with syntax and examples. The Microsoft Access DLookup function returns a value from an Access table (or domain).

www.techonthenet.com

 

[SHANEMAC51]

KNDCR.entry = 10105238703

this number is too big for ACCESS 32x(more 2 000 000 000+ if it's a long integer or a counter)
of course you can have ACCESS 64x

 

[SHANEMAC51]

With rstupd

couldn't find END WITH
and END SUB

 

[oleronesoftwares]

Welcome @Jallan! to the best online forum for Ms Access developers.