Solved

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

Posted on 2016-10-19
7
50 Views
Last Modified: 2016-10-20
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
Comment
Question by:iainmacleod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41851575
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41851599
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41851768
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:iainmacleod
ID: 41851787
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 41851837
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
 

Author Closing Comment

by:iainmacleod
ID: 41851938
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
 
LVL 57
ID: 41851966
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question