Need Help with SQL Statement in Code... 1

[JonWolgamuth]

I'm attempting to add to a record using a SQL statement in code behind a form. I have an option group indicating which queue a piece of work is going into, then I want the SQL statement to look up the actual queue name from the table and paste the name (queue name, not the queue number) to the table. Here's the statement as I have it now...

QueueIn = "SELECT FixedQueuesTBL.QueueName FROM FixedQueuesTBL WHERE (((FixedQueuesTBL.ID)=[QueueOptionGRP]));"

I'm getting the following error which points to this line.

"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

The field "QueueIn" is a text field and the field size is set to 50.

Any ideas what I'm doing wrong???

Thanks in advance!

 

[DougP]

what is QueueOptionGRP? a text box?

try this

SELECT FixedQueuesTBL.QueueName FROM FixedQueuesTBL WHERE FixedQueuesTBL.ID = '" & me![QueueOptionGRP] & "';" DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[JonWolgamuth]

QueueOptionGRP is an option group which gives a numeric value that corresponds to a queue name in the FixedQueuesTBL. I want the Queue name, not the number, to be included in the main table.

Sorry for the late response on this, and thanks for your help!

 

[JonWolgamuth]

I found a problem with my code. I increased the field size to 255 for the QueueIn column, and it worked, except it pasted the SELECT statement itself to the table, not the RESULTS of the SELECT statement.

Any ideas?????

 

[jebry]

Hi!

Try this code:

Dim rst As DAO.Recordset
Dim sql As String

sql = "SELECT FixedQueuesTBL.QueueName FROM FixedQueuesTBL WHERE FixedQueuesTBL.ID = '" & me![QueueOptionGRP] & "';"
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

If rst.EOF = True And rst.BOF = True Then
QueueIn = ""
Else
rst.MoveFirst
QueueIn = rst!QueueName
End If

set rst = Nothing

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JonWolgamuth]

Jeff, thanks for your input! I appreciate the direction.

I'm getting a "Data Type Mismatch in Criteria" pointing to this line:

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

Here's what I have thus far, based on what you sent me. (Notice that I've changed the name of the tables).

Dim rst As DAO.Recordset
Dim sql As String

sql = "SELECT VARQueuesTBL.QueueName FROM VARQueuesTBL WHERE VARQueuesTBL.ID = '" & Me![QueueOptionGRP] & "';"
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

If rst.EOF = True And rst.BOF = True Then
QueueIn = ""
Else
rst.MoveFirst
QueueIn = rst!QueueName
End If

Set rst = Nothing

Also, for my own learning benefit, what is the EOF and BOF? I'm confused...

 

[jebry]

Hi!

EOF means end of file and BOF means beginning of file. EOF is set to true if you are on or before the last record BOF is the same for the first record. They will both be true if there are no records. So this is the basic test for an empty recordset. As for the error message, it probably means that VARQueuesTBL.ID is defined as a numerical field. If that is the case, then delete the single quotes in the sql assignment statement.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JonWolgamuth]

Jeff! Thanks for your feedback and help!

Problem has been resolved, and it works like a charm!

Thanks again...