Text box value as query conditional. Syntax problem ?

[MrJRW]

Good Morning.

This one has me really, really, confused. (and frustrated !)

This "mdb" is to track warranty claims received.

Table 1: tblClaimNumber, one field: ClaimNumber. Integer - defined as four (4) digits: 0001, 0002, etc

Table 2: tblWarrClaimLog 5 fields:

LogNumber (text)
DateReceived (Date)
Company (text)
Comments (text)
SEQ (number, integer)

On my data entry form I have ALL text boxes.

The LogNumber control (tbxLogNumber]is defaulted to a string of "WARR" & Year(Date()) & tblClaimNumber.ClaimNumber

The tblClaimNumber.[ClaimNumber] is sucessfully incremented when I leave the Comment text box, so the LogNumber changes for the next record

? I need to "copy" the tblClaimNumber.[ClaimNumber] value into the SEQ field of the other table.

This query DOES work:

UPDATE tblWarrClaimLog, tblClaimNumber
SET tblWarrClaimLog.[SEQ]= tblClaimNumber.[ClaimNumber]
WHERE tblWarrClaimLog.[LogNumber]='WARR-2012-0001'

So I just need to modify the WHERE conditional

Instead of the 'hard string' I'm trying to use the changing LogNumber string to direct the update to the proper record.

Using:
strLogNumber = Me.tbxLogNumber.Value, or
Forms![frmWARR_NewClaims]!tbxLogNumber] instead of WARR-2012-0001

Does not work

? Any ideas ?

Thank-you for your help
MrJRW

 

[MazeWorX]

Try

Forms![frmWARR_NewClaims]![blue][[/blue]tbxLogNumber]

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MrJRW]

Whoops ! Typo in my write up.

What I have coded is:

Dim strSQL As string

strSQL =
"UPDATE tblWarrClaimLog, tblClaimNumber " & _
"SET tblWarrClaimLog.[SEQ] = tblClaimNumber.[ClaimNumber] " & _
"WHERE tblWarrClaimLog.[LogNumber]= ' " & Forms![frmWarr_NewClaims]![tbxLogNumber] & " '; "

DoCmd.RunSQL strSQL

 

[MazeWorX]

Have you tried a debug.print to check the value of the control?

also

The LogNumber control (tbxLogNumber]is defaulted to a string of "WARR" & Year(Date()) & tblClaimNumber.ClaimNumber. Which would give you "WARR20120001" but the test data where the query works is 'WARR-2012-0001'

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MrJRW]

MazeWorX,

Thank-you for trying to help me.

In the control for the Log Number, (tbxLogNumber) I set the Default value to:

="WARR-" & Year(Date()) & "-" & Format$(DLookup("[ClaimNumber]","tblClaimNumber"),"\0000")

Hence, the "-"s.

I don't know how to you debug.print

MrJRW

 

[PHV]

You have spurious spaces here:
"WHERE tblWarrClaimLog.[LogNumber]= '[highlight] [/highlight]" & Forms![frmWarr_NewClaims]![tbxLogNumber] & "[highlight] [/highlight]'; "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrJRW]

Mr. PHV,

Thank-you for your help.

Removed spaces.
Tested....

No joy

In my earlier post I ment to say I don't know how to use debug.print (not you debug.print)

Now you see why this issue has been vexing (great Scrabble word by-the-way) me !

Again, thank-you for your help

MrJRW

 

[MazeWorX]

add into your code

debug.print Me.tbxLogNumber

It should print the value of the control in the immediate window when the code is executed

or even a debug.print strSQL will print the SQL

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MrJRW]

My Sub:

Private Sub tbxNameofParty_Exit(Cancel As Integer)

Dim strSQL As String
Dim strLogNumber As String

strLogNumber = Me.tbxLogNumber.Value

strSQL = "UPDATE tblWarrClaimLog, tblClaimNumber " & _
"SET tblWarrClaimLog.[SEQ] = tblClaimNumber.[ClaimNumber] " & _
"WHERE tblWarrClaimLog.[LogNumber]= '" & Forms![frmWARR_NewClaims]![tbxLogNumber] & "';"


Debug.Print Me.tbxLogNumber



' This did not work "WHERE tblWarrClaimLog.[LogNumber]= '" & strLogNumber & "';"
' This works when hardwired "WHERE tblWarrClaimLog.[LogNumber]= 'WARR-2012-0001';"



DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True



End Sub

Adding in the debug.print Me.tbxLogNumber or debug.print strSQl did nothing

JRW

 

[MazeWorX]

ok obviously we are having an issue with your familiarity with the vbe interface so ... lets add this instead of the debug


MsgBox strSQL

this should pop the SQL up as a msg box copy the text and paste it here

also add the line

MsgBox Me.tbxLogNumber.Value

and post it just for fun

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MrJRW]

This is too weird.

The MsgBox for strSQL yields:

UPDATE tblWarrClaimLog, tblClaimNumber SET tblWarrClaimLog.[SEQ] = tblClaimNumber.[ClaimNumber] WHERE tblWarrClaimLog.[LogNumber]='WARR-2012-0090';

ClaimNumber was at 0090 when I ran this.

The MsgBox for Me.tbxLogNumber.Value gave:

WARR-2012-0090

BUT !!!

the value of tblWarrClaimLog.[SEQ] is still 0 !

So if I hardcode the stuff between the single quotes, [SEQ] gets set to the correct value (90), but If I use some type of "variable", (i.e. the value of the control), [SEQ] does not get updated.

Weird.

JRW

 

[MazeWorX]

A possible solution would be to get the value from the control?


SET tblWarrClaimLog.[SEQ] = Right(Me.tbxLogNumber,4)

Also for testing comment out the SetWarnings lines so we can see how the query is giving errors. Try it as is first with warnings commented out

thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MrJRW]

Problem solved

I was setting the value of LogNumber via the Default Value property

When I changed to setting the value of Me.LogNumber.Value in an event (LogNumber_GotFocus), the update query worked !

Thank-you all who helped.

MrJRW