I need help recoding my Access 2000 VBA code to Access 97

[SheilaAlighieri]

Hi! Can anyone help me to recode this Access 2000 VBA code so it is compatible with Access 97? I really don't know how to.

Maybe one of you can help me with another small problem. This code selects all records in my table budget and then compares it to the data entered in a form (to which this code is attached). When the data is similar, it doesn't copy the data to the table. When it's unique, the data is copied. I only have some difficulties comparing the fields "BudgetDate" and "InvoiceDte", cause the Budgetdate is the Invoicedate + one year. I coded it as follows in rst.AddNew: rst!BudgetDate = DateAdd("yyyy", 1, InvoiceDte). But I don't know how to code this in rst.open.





Private Sub Button__Add__Click()
On Error GoTo Err_Button__Add__Click

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic


If (MsgBox("The system will now copy this invoice to the budget of next year.", vbInformation + vbOKCancel, "Confirm&quot = vbOK) Then

rst.Open "SELECT * " _
& "FROM Budget " _
& "WHERE (AccountCode = " & AccountName & &quot " _
& "AND (PlanRegion = '" & PlanRegion & "') " _
& "AND (Country = '" & Country & "')" _
& "AND (Place = '" & Place & "') " _
& "AND (Channel = '" & Channel & "')" _
& "AND (CodeCategory = " & CategoryName & &quot" _
& "AND (CodeSort = " & SortName & &quot" _
& "AND (Description = '" & Description & "');"


If rst.EOF Then
rst.AddNew
rst!AccountCode = AccountName
rst!BudgetDate = DateAdd("yyyy", 1, InvoiceDte)
rst!Budget = Amount / Rate
rst!Planned = Amount / Rate
rst!PlanRegion = PlanRegion
rst!Country = Country
rst!Place = Place
rst!Channel = Channel
rst!CodeCategory = CategoryName
rst!CodeSort = SortName
rst!Description = Description

rst.Update

DoCmd.GoToRecord , , acNext
DoCmd.Beep
Call MsgBox("Invoice copied.", vbInformation, "Success&quot

Else

DoCmd.Beep
MsgBox "This invoice is already in next year's budget.", vbCritical, "Error"

End If

rst.Close
Set rst = Nothing

End If

Exit_Err_Button__Add__Click:
Exit Sub

Err_Button__Add__Click:
DoCmd.Beep
MsgBox "You haven't entered all required data", vbCritical, "Error"
Resume Exit_Err_Button__Add__Click

End Sub

Regards,

Sheila

 

[SheilaAlighieri]

Hi all
I managed to recode above-mentioned code myself. I am still stuck with the other problem though

 

[PaulBricker]

Sheila, I'm not entirely clear on where your values in the Select statement are coming from( the one's enclosed in the ampersands " & AccountName & &quot If they are controls on your form then you can do something like this to get your BudgetDate

Dim myDte as Date
myDte = DateAdd("yyyy",1,Forms!FormName!InvoiceDte)

rst.Open "SELECT * " _
& "FROM Budget " _
& "WHERE (AccountCode = " & AccountName & &quot " _
& "AND (PlanRegion = '" & PlanRegion & "') " _
& "AND (Country = '" & Country & "')" _
& "AND (Place = '" & Place & "') " _
& "AND (Channel = '" & Channel & "')" _
& "AND (CodeCategory = " & CategoryName & &quot" _
& "AND (CodeSort = " & SortName & &quot" _
& "AND (BudgetDate = #" & myDte & "#)" _
& "AND (Description = '" & Description & "');"

 

[SheilaAlighieri]

You got it right Paul But when I use this code, all invoices are copied into next year's budget, even those already in it.

 

[SheilaAlighieri]

Hi Paul,

I noticed something strange, my field is formatted as a short date (both in my table and form). When the day equals 13th day of the month or higher.. the code works. But when you enter any number equal or below 12, the invoice is copied, even when it is already in my budget table.

 

[SheilaAlighieri]

Oh one more thing Is this same method useable in case of the following?

Dim MyBudget as ???
MyBudget = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate

Here I divide the amount by the currency rate.

in rst.AddNew I coded it as follows:

Budget = Amount / Rate

 

[PaulBricker]

My first thought is there is something wrong in your date format. I ran the code against dates less than and greater than those days and didn't have any problems. The fact that the break comes between the 12th and 13th makes me think Months and Days are somehow reversed somewhere. Check your Settings in Control Panel under Regional Settings. Also, debug.print your string and see what values are being returned by the Form Controls. This will help you see that you are comparing apples to apples.

Debug.Print "SELECT * " _
& "FROM Budget " _
& "WHERE (AccountCode = " & AccountName & &quot " _
& "AND (PlanRegion = '" & PlanRegion & "') " _
& "AND (Country = '" & Country & "')" _
& "AND (Place = '" & Place & "') " _
& "AND (Channel = '" & Channel & "')" _
& "AND (CodeCategory = " & CategoryName & &quot" _
& "AND (CodeSort = " & SortName & &quot" _
& "AND (BudgetDate = #" & myDte & "#)" _
& "AND (Description = '" & Description & "');"

The variable MyBudget should work fine. If your values are currency, declare it as currency. If not, declare it as Double.

Paul

 

[SheilaAlighieri]

Hi Paul,

Could you inform me a bit more of the debug.print option? WHat does it do and where do I put it in my code?

I checked my settings and they seem to be in order. I use this code, it is correct, isn't it?

Dim db As Database
Dim rst As DAO.Recordset
Dim myDte As Date
Dim myBdgt As Currency
Dim myPlnnd As Currency
Set db = CurrentDb

myDte = DateAdd("yyyy", 1, Forms![Data Entry]!InvoiceDte)
myBdgt = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate
myPlnnd = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate

Set rst = db.OpenRecordset("SELECT * " _
& "FROM Budget " _
& "WHERE (AccountCode = " & AccountName & &quot " _
& "AND (BudgetDate = #" & myDte & "#) " _
& "AND (Budget = " & myBdgt & &quot " _
& "AND (Planned = " & myPlnnd & &quot " _
& "AND (PlanRegion = '" & PlanRegion & "') " _
& "AND (Country = '" & Country & "')" _
& "AND (Place = '" & Place & "') " _
& "AND (Channel = '" & Channel & "')" _
& "AND (CodeCategory = " & CategoryName & &quot" _
& "AND (CodeSort = " & SortName & &quot" _
& "AND (Description = '" & Description & "');&quot

If (MsgBox("The system will now copy this invoice to the budget of next year.", vbInformation + vbOKCancel, "Copy&quot = vbOK) Then

If rst.EOF Then
rst.AddNew
rst!AccountCode = AccountName
rst!BudgetDate = DateAdd("yyyy", 1, Forms![Data Entry]!InvoiceDte)
rst!Budget = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate
rst!Planned = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate
rst!PlanRegion = PlanRegion
rst!Country = Country
rst!Place = Place
rst!Channel = Channel
rst!CodeCategory = CategoryName
rst!CodeSort = SortName
rst!Description = Description

rst.Update

DoCmd.GoToRecord , , acNext
DoCmd.Beep
Call MsgBox("Invoice copied.", vbInformation, "Copied&quot

Else

DoCmd.Beep
MsgBox "This invoice is already in next year's budget.", vbCritical, "Error"

End If

rst.Close
Set rst = Nothing

End If

Exit_Err_Button__Add__Click:
Exit Sub

Err_Button__Add__Click:
DoCmd.Beep
MsgBox "You haven't entered all required data", vbCritical, "Error"
Resume Exit_Err_Button__Add__Click

End Sub

Thanks,

Sheila

 

[PaulBricker]

Sheila, try this. Copy and paste this code in a new module in your database.
Sub checksql()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim myDte As Date
Dim myBdgt As Currency
Dim myPlnnd As Currency
Set db = CurrentDb
myDte = DateAdd("yyyy", 1, Forms![Data Entry]!InvoiceDte)
myBdgt = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate
myPlnnd = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate
strSQL = ("SELECT * " _
& "FROM Budget " _
& "WHERE (AccountCode = " & AccountName & &quot " _
& "AND (BudgetDate = #" & myDte & "#) " _
& "AND (Budget = " & myBdgt & &quot " _
& "AND (Planned = " & myPlnnd & &quot " _
& "AND (PlanRegion = '" & PlanRegion & "') " _
& "AND (Country = '" & Country & "')" _
& "AND (Place = '" & Place & "') " _
& "AND (Channel = '" & Channel & "')" _
& "AND (CodeCategory = " & CategoryName & &quot" _
& "AND (CodeSort = " & SortName & &quot" _
& "AND (Description = '" & Description & "');&quot

Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)
Debug.Print strSQL

End Sub

Look to see if your immediate window is open in the module. If you don't see it, go to View....Immediate Window on the Menu Bar. This is the Debug Window.
Then on the menu bar go to Debug....Step Into.
This will start the code running. You should either get an error message is something or the first line of the code will turn yellow. Then on the Menu bar go to
Debug...Step Out. This will run the code all the way thru and should print your SQL statement in the Debug window. Look at that result. It should show all the values for the fields surrounded by the ampersands like
& AccountName &
& myDte &

Double check all those values to be sure the code is seeing them correctly. Make sure the Form is open. You can select different records to run this against. Ones that are before the 12 and ones that are after the 12 to see what is being returned for the value myDte. It may show you something.

Try it and post back any problems.

Paul

 

[SheilaAlighieri]

I really appreciate your hulp Paul I am sorry to ask so much of you, but now I get a runtime error, namely '3075'. It mentions the following:

Extra ) in query expression '(AccountCode = ) AND (BudgetDate = #13-12-81#) AND (Budget = 0,5113) AND (Planned = 0,5113) AND (PlanRegion = &quot AND (Country = &quot AND (Place = &quot AND (Channel = &quot AND (CodeCategory = )AND (CodeSort = ) AND (Description = &quot'

 

[PaulBricker]

Sheila,
the Budget and Planned fields return the same value 0,5113. How is that value arrived at. Those values don't seem consistent with the expressions
myBdgt = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate
myPlnnd = Forms![Data Entry]!Amount / Forms![Data Entry]!Rate

Are either Amount or Rate calculated values. If they are, look for an error there as well. You could post those calculation for us to look at also.

Also, your BudgetDate = 13/12/81
I assume this is formatted as dd/mm/yy (day/month/year)
Correct. Under your regional setting in Control Panel is that what your Short Date format is set to

D/m/yy

Are all the dates in the Table formatted dd/mm/yy.

Paul

 

[PaulBricker]

P.S. if you would like to send me a small sample db it might help to straighten this out quickly. I'll be gone for a few hours but will look when I return.
My email is pbricker@attbi.com

Paul