Solved

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

Posted on 2016-10-19
7
33 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
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 49

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 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