Update problem (1 Viewer)

B

Brian Martin

Registered User.

Local time

Today, 05:09

Joined

Jul 24, 2002

Messages

68

• Mar 9, 2004

• #1

Nothing is being updated What is wrong with this code?:
SQLstring = "UPDATE [Product List] SET [Full Description] =" & [Forms]![frmChoose]![subChoose].[Form].Controls![FD] & "WHERE [Part Number] =" & [Forms]![frmChoose]![subChoose].[Form].Controls![PN] & ";"

 

Mile-O

Back once again...

Local time

Today, 05:09

Joined

Dec 10, 2002

Messages

11,316

• Mar 9, 2004

• #2

Put a space before the WHERE

 

dcx693

Registered User.

Local time

Today, 00:09

Joined

Apr 30, 2003

Messages

3,265

• Mar 9, 2004

• #3

Try:

Code:Copy to clipboard

SQLstring = "UPDATE [Product List] SET [Full Description] ='" & [Forms]![frmChoose]![subChoose].[Form].Controls![FD] & "' WHERE [Part Number] =" & [Forms]![frmChoose]![subChoose].[Form].Controls![PN] & ";"

I'm pretty sure you can also remove the .Controls reference since it's the default collection of the Form property like this:

Code:Copy to clipboard

SQLstring = "UPDATE [Product List] SET [Full Description] ='" & [Forms]![frmChoose]![subChoose].[Form]![FD] & "' WHERE [Part Number] =" & [Forms]![frmChoose]![subChoose].[Form]![PN] & ";"

I am assuming that [Full Description] is a text field and that [PN] is numeric.

 

B

Brian Martin

Registered User.

Local time

Today, 05:09

Joined

Jul 24, 2002

Messages

68

• Mar 10, 2004

• #4

Still stuck

Both fields are text fields.
i now have the following and it still doesn't work.

SQLstring = "UPDATE [Product List] SET [Full Description] =" & [Forms]![frmChoose]![subChoose].[Form].Controls![FD] & _
" WHERE [Part Number] = " & [Forms]![frmChoose]![subChoose].[Form].Controls![PN] & ";"

Any more thoughts?

 

Mile-O

Back once again...

Local time

Today, 05:09

Joined

Dec 10, 2002

Messages

11,316

• Mar 10, 2004

• #5

Code:Copy to clipboard

SQLstring = "UPDATE [Product List] SET [Full Description] = """ & [Forms]![frmChoose]![subChoose].[Form].Controls![FD] & _
""" WHERE [Part Number] = """ & [Forms]![frmChoose]![subChoose].[Form].Controls![PN] & """;"

 

B

Brian Martin

Registered User.

Local time

Today, 05:09

Joined

Jul 24, 2002

Messages

68

• Mar 10, 2004

• #6

No luck

tried your code and it didn't work either. I'm stumped. the reason I'm trying to do this instead of a regular query is because I have a memo field that just wont allow anything greater than 255 characters and i read in this forum that some people solved this problem by doing their queries in code. I can't even add more than 255 when directly typing into the table. Any other ways of fixing this?

 

R

Rich

Guest

• Mar 10, 2004

• #7

Why doesn't your query allow a memo field greater than 255?

but then if you can't type more than 255 directly into the table the field isn't defined as Memo

 

B

Brian Martin

Registered User.

Local time

Today, 05:09

Joined

Jul 24, 2002

Messages

68

• Mar 10, 2004

• #8

Getting there

It is a memo. I have partly sorted the problem. i had set the format of the memo field as > to make all the text capitals. i deleted that and now it lets me input more than 255. But when I run an append query I'm sometimes getting gobbldeegook added on to the end.

 

R

Rich

Guest

• Mar 10, 2004

• #9

If you apply formatting to a memo field then the query will restrict the Memo output to 255