VB and SQL confusion

[ajetrumpet]

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

that doesn't tell me much, Wayne. Where and what am I looking at?? where is the event that runs the code? and on what control??

[Wayne311]

that doesn't tell me much, Wayne. Where and what am I looking at?? where is the event that runs the code? and on what control??

The control is the button "New Case #" Event On Click Private Sub Command11.

Wayne

[ajetrumpet]

I'm sorry Wayne.

when I put in "c-99-99" in a new record in that subform, it automatically inserts a new record in the form AND in the RCPTcrDISTRICT table, without pushing the button.

the first thing you absolutely need to do, is to stop those recs from automatically be entered, especially if they DON'T belong in that 'rcptDistrict' table, because that's where the automation is happening at.

I'm really inclined to ask what your business is, because as it seems now, the setup is very wrong, and it's not into production a great deal at this point, which means it could be redone with minimal work so it is setup right.

basically, what you need is for the button to add a new rec in that subform by CLICKING on it ONLY. you need to change the "allow additions" property to "no", and change it on click of the button, but that can be changed if the setup was changed as well.

do you have to time to do this sort of backpedaling??

[Wayne311]

That is the way it is supposed to work. A clerk gets a new case and creates a new case number. If someone makes a payment on an old case the clerk enters the old case number. The only table to have just new case numbers is the table (CaseNumber).

Yes I can back pedal, but as I see it, it works as intended.

Wayne

[ajetrumpet]

so what is the issue exactly, Wayne??

The table that receives the record, based on what the clerk enters into the subform as a new record?? that's the way i see it.

and if that's correct, what I mentioned in the early post still needs to be changed, because old case numbers will still be entered into the "case number" table where you want new ones, as you have it now.

see what I mean?

[Wayne311]

The clerk enters data in the form which puts it in RcptCRDistrict table. I want the last used case number also stored in the table CaseNumber where future case numbers will be generated, stored then deleted as they are used. I only want 1 record to be stored in CaseNumber table.

If you want to get business details...I am available by email or phone for that.

Wayne

[Wayne311]

I re-read that last post...It should be that I want the last new created case number also stored in the CaseNumber table.

That sounds better.

[ajetrumpet]

well Wayne,

I don't want to say this really, but talking to you on a phone would be much more beneficial to you than these thread communications would be, but unfortunately I don't have the time during the daytime hours.

send me a message if you want to get in contact. You're really not supposed to do that through forums like this, but I doubt anyone will mind if you eventually post the solution you got through this forum thread. that's really the big issue on these forums.

so if you agree to do that, send me a message. I personally don't mind one way or the other, but getting this kind of help to you through the thread is going to take forever, considering the way it's going right now.

[Wayne311]

That is OK with me. I assume that you can send me an email to arrainge the phone contact. I set my account up so members can email me.

Wayne

[ajetrumpet]

sent.........

[ajetrumpet]

hey Wayne,

I was taking a look at your file's VBA, and what it looks like is that the second INSERT INTO line's concatenation was off a bit. try this:

CurrentDb.Execute "INSERT INTO CaseNumber ( CRNumber ) VALUES 

('C" & CStr(Right(Year(Date), 2)) & " - " & CStr(intIncrementalNumber + 1) & "');"

without the linefeeds of course.

[Wayne311]

OK that works...thanks.

I would have never thought of the bizarre quotes on the 'C". I did try the str but not CStr.

Thanks again
Wayne

[ajetrumpet]

no problem, sir. remember too, that more often than not, VBA hates single quote marks. Better to use doubles. One double can be denoted via the chr() function, or by writing 4 doubles in a row ("""").

[maximus]

Hello Wayne.

Can you give me some more details to understand you problem better.

Here are some questions that I have:
1) CNumber this Field in your table is it a number feild or a text feild.
2) If it is Not why have you declared intincrementalNumber as integer.

This is what i assume is the problem here you have a text feild which has alpha numerical data. Like C112, C113.

You want to create and incremental sequence of these numbers.

if my understanding is correct pls drop me a private msg or make a further post here I will be glad to assist.

[Wayne311]

Hello Wayne.

Can you give me some more details to understand you problem better.

Here are some questions that I have:
1) CNumber this Field in your table is it a number feild or a text feild.
2) If it is Not why have you declared intincrementalNumber as integer.

This is what i assume is the problem here you have a text feild which has alpha numerical data. Like C112, C113.

You want to create and incremental sequence of these numbers.

if my understanding is correct pls drop me a private msg or make a further post here I will be glad to assist.

CNumber is a number field. It is used to create an incremental sequence (string) as you suggest.

The current issue has been resolved. But I am still working on changes for a new year and some checks. This is the code so far.

Private Sub Command11_Click()
'Button "New Case#"
On Error GoTo Err_NewCaseNumber_Click
Dim intIncrementalNumber As Integer 'Dim = Declare variable
Dim strMsgbx As String
Dim strSQL As String
'If Int(Right(Year(Date), 2)) <> (DMax("[Yr]", "CaseNumber")) Then
' CurrentDb.Execute "INSERT INTO CaseNumber( CNumber )VALUES ( 0 );"
' DoCmd.OpenTable "CaseNumber", acViewNormal, acEdit
' DoCmd.GoToRecord acTable, "CaseNumber", acFirst
'DoCmd.RunCommand acCmdDeleteRecord
'If MsgBox(You are about to delete a line. Do you really want to do this?", vbYesNo + vbCritical + vbDefaultButton2, "Warning") = vbYes Then
' DoCmd.SetWarnings False
' DoCmd.RunCommand acCmdDeleteRecord
' DoCmd.SetWarnings True
'End If

'End If
'intIncrementalNumber is the maximum CNumber
intIncrementalNumber = IIf(IsNull(DMax("[CNumber]", "CaseNumber")), 0, DMax("[CNumber]", "CaseNumber"))
'MsgBox intIncrementalNumber
'Check if the year has changed.
'MsgBox Int(Right(Year(Date)))

'If it has then reset CNumber to 0
MsgBox Int(Right(Year(Date), 2))
MsgBox (DMax("[Yr]", "CaseNumber"))
If IsNull(Forms(Me.Parent.Name).Controls("RcptCRDistr ict Subform1").Form!CRNumber) = False Then
MsgBox ("Field must be empty to create a case number")
Exit Sub
End If
'Increment CNumber +1 and insert into form
Me.[CRNumber] = "C" & Right(Year(Date), 2) & "-" & intIncrementalNumber + 1
'Insert new C??-???? into Case Number in form and table
CurrentDb.Execute "INSERT INTO CaseNumber( CNumber, CRNumber )VALUES ((" & intIncrementalNumber + 1 & "),('C" & CStr(Right(Year(Date), 2)) & " - " & CStr(intIncrementalNumber + 1) & "'));"

Exit_NewCaseNumber_Click:
Exit Sub
Err_NewCaseNumber_Click:
MsgBox Err.Description
Resume Exit_NewCaseNumber_Click
End Sub

Wayne