• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

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)
0
iainmacleod
Asked:
iainmacleod
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now