Solved SQL keeps failing on select (1 Viewer)

Jallan!

New member
Local time
Today, 12:43
Joined
Jan 31, 2022
Messages
10
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

Wino Moderator
Staff member
Local time
Today, 12:43
Joined
Aug 30, 2003
Messages
36,118
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

I’m here to help
Staff member
Local time
Today, 12:43
Joined
Oct 29, 2018
Messages
21,358
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

Banishment Pending
Local time
Today, 14:43
Joined
May 11, 2011
Messages
11,611
Code:
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!

New member
Local time
Today, 12:43
Joined
Jan 31, 2022
Messages
10
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

Banishment Pending
Local time
Today, 14:43
Joined
May 11, 2011
Messages
11,611
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.

 

Users who are viewing this thread

Top Bottom