Runtime error 3075 in MS Access VBA after moving Tables to SQL Server

My code works when using tables in another Access Database however I get this error after moving Tables to SQL.
error-3075.JPGThe line is:
Me.netcharge = Round(DSum("NZ(hxdchge,0) + NZ(imhandchge,0)", "invoicedetails", "[invdetailid]=" & Me.invdetailID), 2)
iainmacleodAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dale FyeConnect With a Mentor Commented:
This is one of the few down-sides of using SQL Server when you have been focused on the functionality in Access.

Are you sure you want to sum across the InvDetailID?  Generally, an InvDetailID will refer to a single record in the InvoiceDetails table.

I'm guessing that you don't actually need that DSUM() statement and can simply use:

Me.netcharge = Round(NZ(me!hxdchge,0) + NZ(me!imhandchge,0), 2)
0
 
HuaMinChenBusiness AnalystCommented:
HI,
1. Please run Command to retrieve records from SQL database.
2. Try to put this
	<connectionStrings>
		<add name="conn2" connectionString="Data Source=192.168.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
	</connectionStrings>

Open in new window

within Web.config/app.config file in your project, to ensure the project will work fine with the database.
0
 
Gustav BrockCIOCommented:
The error message is quite clear: You have no value for Me.invdetailID.

Try this:
Dim invdetailID As Long
invdetailID = Nz(Me!invdetailID.Value, 0)

If invdetailID = 0 Then
    ' No ID.
Else
    Me.netcharge = Round(DSum("NZ(hxdchge,0) + NZ(imhandchge,0)", "invoicedetails", "[invdetailid]=" & invdetailID & ""), 2)
End If

Open in new window

/gustav
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Dale FyeCommented:
Unlike Access, SQL Server does not assign autonumber (counter) field values until after a record is saved.  Access will assign these values to the record as soon as you create the new record, but SQL Server does not, so if the record you are on is a New Record, and it has not been saved, then any autonumber field will not contain a value.

HTH
Dale
0
 
iainmacleodAuthor Commented:
Gustav,
Thank you for your input, however I need to assign the InvDetailID prior to running the me.netcharge line as it is a unique identifier that allows me to calculate total charges.

Dale,
Thank you for your explaination. Although I have been using Access for sometime and have just about got me head around VBA code, I am new to SQL. The application I have written is now being used in earnest which is why I was keen to move the tables to SQL. I just need to figure out how to deal with this type of issue as I am sure I will have more like this in the application.

Iain
0
 
iainmacleodAuthor Commented:
Thank you Dale.
I am know wondering why I was using that in the line originally. Your suggestion is much tidier and has solved that particular problem.. Now on to the next.... My learning curve continues!
Many thanks
Iain
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Does invoicedetails represent line items or the invoice header.  

If line items, I would think the error is in WHERE clause and you would want a DSum().

If a header, then as Dale said, no DSum() required as you'd only have one record.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.