Any Visual Basic Gurus out there?

[BraindG]

Chrissys having a problem, just wondering if anyone here knows alot about VB?

[Crickle]

Private Sub AcceptPayment(inPaymentMethod As String)
Dim con As Object
Dim rs, rs2 As Object
Dim strSQL As String
Dim inpaymentid, inCustomerRef, inamountpaid, inservicetype, ReceiptNumber As Integer
Dim inTodaysDate, inservicestartdate, inserviceenddate As Date

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")

' inPaymentMethod = "Cash"

strSQL = "SELECT * FROM Payment WHERE NAME='" & inPaymentMethod & "';"
'MsgBox (strsql)

rs.Open strSQL, con, 1 ' 1 = adOpenKeyset
If (rs.EOF) Then
MsgBox ("error with payment table!")
Exit Sub
Else
inpaymentid = rs.Fields("ID")
End If
rs.Close

inCustomerRef = Me.HiddenCustomerID.Caption
inTodaysDate = Date
inamountpaid = Me.Text53.Value
inservicestartdate = Me.Text37.Value
inserviceenddate = Me.Text39.Value
inservicetype = Me.Combo125.ItemData(Me.Combo125.ListIndex)
ReceiptNumber = Me.ReceiptNumber.Value

strSQL = "INSERT INTO Account_Transactions " & _
"(ServiceRef,CustomerRef,InsertedDate,ReceiptNumber ,AmountPaid,PaymentRef,ServiceStartDate,ServiceEnd Date) " & _
"VALUES (" & inservicetype & "," & _
inCustomerRef & ",'" & _
ReceiptNumber & ",'" & _
inTodaysDate & "'," & _
inamountpaid & "," & _
inpaymentid & ",'" & _
inservicestartdate & "','" & _
inserviceenddate & "');"


rs2.Open strSQL, con, 1 ' 1 = adOpenKeyset
' rs2.Close
Recalculate_Current_Customer_Service_Level (inCustomerRef)
Text0_AfterUpdate

End Sub

This one is the problem! can anyone help? I'm trying to create an autonumber in 0001 format for every transaction! I jusyt don't seem to see where its falling down! Its not placing ReceiptNumber in the columns of the created form 'transaction log' butit does the rest!

[AderC]

If you insert a breakpoint at the INSERT step and run the code, what's the value of ReceiptNumber when it reaches the breakpoint?

aDe

[Si.]

Surely your auto number is created in the database as a rule to the field when you create the table......

And i don't really get what you mean by "Its not placing ReceiptNumber in the columns of the created form 'transaction log' butit does the rest!"

ReceiptNumber is placed into the Account_Transactions table (although you have some spaces in the field names which i would imagine need correcting.) one thing when inserting into the table you have the

ReceiptNumber & ",'" & _
inTodaysDate & "'," & _

in the wrong order. insert values should appear in the same order as the fields of the table in the sql string if you get me.


so to explain you insert statement puts the receiptnumber in the insertdate and the date in the receiptnumber field

[Crickle]

Thanks Si, I'm looking into it now.

http://www.daniweb.com/techtalkforums/thread17610.html

I'm working with old code here thats not mine so bare will me lol.

Ok, I hope this makes more sense:

The code currently generates an autonumber, however it creates a random number rather than a rolling number that goes up by one for each record. i want a rolling number but cannot get the code to generate it that way for the table it creates.

I have checked through the code, its in ADO which is just lazy for whoever wrote it. there is no need for this! its 22 pages long and has routines all through with call statements to run commands. Its a pile of poop!

[Crickle]

If you insert a breakpoint at the INSERT step and run the code, what's the value of ReceiptNumber when it reaches the breakpoint?

aDe

I'm not sure how to do this!

[Crickle]

Thats It - I Quit!/pan /pan /pan /pan /pan /pan /pan /Grrr /Grrr /Grrr

[BraindG]

/haz :P /grouphug

[Nutter_John]

Thanks Si, I'm looking into it now.

http://www.daniweb.com/techtalkforums/thread17610.html

I'm working with old code here thats not mine so bare will me lol.

Ok, I hope this makes more sense:

The code currently generates an autonumber, however it creates a random number rather than a rolling number that goes up by one for each record. i want a rolling number but cannot get the code to generate it that way for the table it creates.


Seeing that you are writing to a DB why not set the field up as an auto incremental one and rather than generate just request a new line in the table .

With sql you can set the start number and how many it goes up by each time a new insert is done . Not a VB programer but write in perl and php and if I had 22 pages of code to check and fix I would just start from scratch as it would be cleaner and optermised .

just my 0.002 pence worth

[Crickle]

Seeing that you are writing to a DB why not set the field up as an auto incremental one and rather than generate just request a new line in the table .

just my 0.002 pence worth

I have tried that but its linked to several other tables all by the same silly random ID autonumber! I was unable to edit the criteria in the tables because of the relationships. I then had a closer look and found that the whole script is based on the input of the daily transactions to that table. Its uploaded as a text file later in the day along with collections of information to a server to restart peoples broadband!

its a pile of fecking cr*p, seriously, its baaaad!
I fee like rebuilding the whole damn thing, but its only suuposed to be a small fixed to the access db and after that I start to build the new system, its going to need a lot of data cleansing too!

OMg, getting overwhelmed again /drinkswine/juggle

stuff this, i'm off to the gym to clear my head!

[Si.]

Can't see where your getting the number from, so maybe that comes from another function.

Is the code inserting anything to the database at all? or does it crash before that?