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?
 
Dale FyeCommented:
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
 
HuaMin ChenProblem resolverCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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)President / OwnerCommented:
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.