VB and SQL confusion

[Wayne311]

I am trying to create a value that will be used in a form. It is generated from a table (CaseNumber) which has only one record in it. I need to create the value then append it to the table and form. Then delete the origional record...

Private Sub Command11_Click()
Dim intIncrementalNumber As Integer
intIncrementalNumber = IIf(IsNull(DMax("[CNumber]", "CaseNumber")), 0, DMax("[CNumber]", "CaseNumber"))
Me.[CRNumber] = "C" & Right(Year(Date), 2) & "-" & intIncrementalNumber + 1
DoCmd.RunSQL "INSERT INTO CaseNumber( CNumber )VALUE (intIncrementalNumber +1);"



Can I use a variable from VB in an SQL statement? (intIncrementalNumber)

Thanks
Wayne

[Wayne311]

Sorry I forgot to mention....How do I get rid of the message box that will come up asking the user if they want to append the table with VB?

Thanks
Wayne

[ajetrumpet]

change:

DoCmd.RunSQL "INSERT INTO CaseNumber( CNumber )VALUE (intIncrementalNumber +1);"

to:

DoCmd.RunSQL "INSERT INTO CaseNumber( CNumber ) VALUES (" & intIncrementalNumber +1 & ");"

and to suppress the message, change:

DoCmd.RunSQL

to:

currentdb.execute

[Wayne311]

Thanks that was helpful...until I tried it with this line: CurrentDb.Execute "INSERT INTO CaseNumber ( CRNumber ) VALUES ('C' " & Right(Year(Date), 2) & "-" & intIncrementalNumber + 1 & ");"

The error is that it is missing an operator?? [CRNumber] is a text field. Any ideas?

Wayne

[ajetrumpet]

Thanks that was helpful...until I tried it with this line: CurrentDb.Execute "INSERT INTO CaseNumber ( CRNumber ) VALUES ('C' " & Right(Year(Date), 2) & "-" & intIncrementalNumber + 1 & ");"

The error is that it is missing an operator?? [CRNumber] is a text field. Any ideas?

Wayne

Wayne,

I'm not sure from looking at this what you want, but do what the pros do. Put that entire statement inside of a string variable and print it out in the imm. window. look at it, then you'll know if it's right or not. so do:

var = "INSERT INTO CaseNumber ( CRNumber ) VALUES ('C' " & Right(Year(Date), 2) & "-" & intIncrementalNumber + 1 & ");"

debug.print var

'break point on next line to check printout

[Wayne311]

I think I am getting bleary eyed on this...second day trying to insert a record into a table (CaseNumber) and the same field in a form.

intIncrementalNumber = IIf(IsNull(DMax("[CNumber]", "CaseNumber")), 0, DMax("[CNumber]", "CaseNumber"))

For the form I use this: Me.[CRNumber] = "C" & Right(Year(Date), 2) & "-" & intIncrementalNumber + 1

It creates C11-123. But I cannot get the same to work in another one-record table that is used to create the string (C11-123). Both of these fields are text. For the table (CaseNumber) I am trying this: CurrentDb.Execute "INSERT INTO CaseNumber ( CRNumber ) VALUES ('C' " & Right(Year(Date), 2) & intIncrementalNumber + 1 & ");"

I have broken it into pieces and I can get the "C11" but not any further.

Wayne

[ajetrumpet]

I think I am getting bleary eyed on this...second day trying to insert a record into a table (CaseNumber) and the same field in a form.

intIncrementalNumber = IIf(IsNull(DMax("[CNumber]", "CaseNumber")), 0, DMax("[CNumber]", "CaseNumber"))

For the form I use this: Me.[CRNumber] = "C" & Right(Year(Date), 2) & "-" & intIncrementalNumber + 1

It creates C11-123. But I cannot get the same to work in another one-record table that is used to create the string (C11-123). Both of these fields are text. For the table (CaseNumber) I am trying this: CurrentDb.Execute "INSERT INTO CaseNumber ( CRNumber ) VALUES ('C' " & Right(Year(Date), 2) & intIncrementalNumber + 1 & ");"

I have broken it into pieces and I can get the "C11" but not any further.

Wayne

I really don't know what you're talking about, Wayne. Your description is very confusing to me. Did you even try to implement the printing technique that I told you about in my last thread?? Seriously, that is what you need to do. if and WHEN you do print it, do you know what you're looking for??

[Wayne311]

Attached is the error I am getting.

Wayne

[Wayne311]

I am not sure what it was supposed to do...It did nothing apparant.

Sorry

[ajetrumpet]

I am not sure what it was supposed to do...It did nothing apparant.

Sorry

that error can mean anything and everything, Wayne. It's one of the great annoyances of Microsoft technology.

It cannot be deciphered by anyone, because in all reality, no pinpointing can be done from it. sorry.

Someone might be able to take a look for you if you upload what you got, what you want, and point people to the EXACT place to fix it for you, so it doesn't take too much time. personally, that's the only way I would do it, because I'm stetched for time as it is.

[Wayne311]

How would I insert a variable (string)?

INSERT INTO CaseNumber ( CRNumber ) VALUES var

Wayne

[ajetrumpet]

INSERT INTO CaseNumber ( CRNumber ) VALUES var

if 'var' is a string, do this:

"INSERT INTO CaseNumber ( CRNumber ) VALUES '" & var & "'"

I don't use VALUES often, so it may need parenthesis too.

remember though, if that VAR has quotes stored inside of it, what I just said means nothing. quotes are the single most annoying entity in access, and visual basic for that matter, according to me.

[Wayne311]

I tried it with double and single quotes (with both and with just each) and get a syntax error...

CurrentDb.Execute "INSERT INTO CaseNumber ( CRNumber ) VALUES '" & strVar & "'

[ajetrumpet]

I tried it with double and single quotes (with both and with just each) and get a syntax error...

CurrentDb.Execute "INSERT INTO CaseNumber ( CRNumber ) VALUES '" & strVar & "'

upload your stuff and tell us what you're needing.

[Wayne311]

Attached is my db. With the RcptData form, the "New Case #" button creates a case number (C11-123) and places it in the form. I want it also to put it in table (CaseNumber, CRNumber) as it did in table (RcptCRDistrict).

Thanks
Wayne