UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error 3075 In Code That Worked Fine Before, Access 2013    
 
   
lemming
post Nov 20 2017, 08:51 PM
Post#1



Posts: 150
Joined: 21-March 14



Hi,

I am suddenly getting Error 3075: |1 in query expression '|2' in a piece of code that has worked well for years. I have put the code below, but what I really want to know is if this is likely a sign of corruption (I tried Compact & Repair to no effect)? After all, why would the query suddenly not work? Should I be spending time trying to fix the code or just export everything to a new database?


'Check whether to copy related records
If MsgBox("Do you want to copy the immigration history?", vbYesNo, "COPY?") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
strSQL = "INSERT INTO [tblImmHistory] ( ClientID, ImmStatus, ImmCode, ImmNote, ImmStart, ImmEnd ) " & _
"SELECT " & Me.ClientID & " as ClientID, ImmStatus, ImmCode, ImmNote, ImmStart, ImmEnd " & _
"FROM [tblImmHistory] WHERE ClientID = " & lOldClientID & ";"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.subImmHistory.Requery
Else
Me.subImmHistory.Form.AllowAdditions = True
End If


Thanks!
Go to the top of the page
 
WildBird
post Nov 20 2017, 10:27 PM
Post#2


UtterAccess VIP
Posts: 3,278
Joined: 19-August 03
From: Perth, Australia


Copy the SQL to a new query window, that will usually point out the issue - usually a missing field or renamed field etc.

If you don't know how to copy SQL etc, let us know.

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 12:01 AM
Post#3


UA Admin
Posts: 31,235
Joined: 20-June 02
From: Newcastle, WA


When something stops working after many months of success, one immediately suspects two things first.

A change such as the things suggested by WildBird.

Data that no longer meets criteria in the code, or which is not of the anticipated datatype.

--------------------
Go to the top of the page
 
lemming
post Nov 21 2017, 08:19 AM
Post#4



Posts: 150
Joined: 21-March 14



Thanks to both of you for the replies. I will investigate both of those possibilities and see what I can find.
Go to the top of the page
 
River59
post Nov 21 2017, 08:27 AM
Post#5



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


QUOTE
"FROM [tblImmHistory] WHERE ClientID = " & lOldClientID & ";"


Is the pipe (|) supposed to be before the control name "OldClientID"? You have "|OldClientID"

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
lemming
post Nov 23 2017, 10:13 AM
Post#6



Posts: 150
Joined: 21-March 14



I have been investigating and the problem seems to start earlier in the code (River59, yes, the l in front of OldClient stands for long - probably lng would be better naming)

The problem I am having is when I am copying controls. I am getting Error 94 Invalid use of Null when trying to copy a null value into aValue(i). For example, if the comments field is null then I get the error - if I add text to the comment field, then there is no error for that field but will be for the next null field.

This has never happened before (e.g. the comment field is often null) so I am at a loss as to why it would suddenly be giving an error.

Any tips on what I should investigate next would be welcome! I have copied the relevant code below. The loop works fine.

Dim ctl As Control
Dim i As Integer: i = 1
Dim aValue(1 To 100) As Variant

For Each ctl In Me.Controls
If ctl.Tag <> "Skip" And (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox) Then
aValue(i) = ctl.Value: i = i + 1
End If
Next ctl
Go to the top of the page
 
DanielPineault
post Nov 23 2017, 10:18 AM
Post#7


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



Since copying every over to a new blank shell takes all of 2 minutes, go ahead and do it, you have nothing to loose.

Your code compiles with errors?
What happens if you debug.print the SQL statement and try and run in as a standard query?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
lemming
post Nov 23 2017, 10:27 AM
Post#8



Posts: 150
Joined: 21-March 14



Hi Daniel,

I did try copying to a blank shell first thing this morning, but I got the same error, which is when I started going through the whole piece of code line by line.


Yes, everything is compiled (and for the last two years it was working fine copying null fields).

I don't think there is a problem with the SQL. What is happening is that the values don't copy properly, and a new record isn't created properly, which means there is no record for the INSERST INTO SQL, but I will do as you suggest.

Kat
Go to the top of the page
 
DanielPineault
post Nov 23 2017, 10:43 AM
Post#9


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



I still perform the operation of Debug.Print and run manually just to see if the values are generating the SQL Statement as expected and see what the QBE says about the query. It would then rule it out completely.

Well, what about simply directly binding to the subform's record source and performing the insert there? Then no Insertion/Requery required.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
lemming
post Nov 23 2017, 10:48 AM
Post#10



Posts: 150
Joined: 21-March 14



Not really sure how to do that - will have to research. This is a side project I manage, I'm not a programmer by training, so I only pick it up once in awhile when there's a problem.
Go to the top of the page
 
DanielPineault
post Nov 23 2017, 10:55 AM
Post#11


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



Sorry, scratch that idea. I just reviewed the original SQL and your approach make more sense.

Could you post the full procedure.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
lemming
post Nov 23 2017, 11:03 AM
Post#12



Posts: 150
Joined: 21-March 14



So here's the thing ... I just tried it (again) and this time it worked fine? Copying same client to same new contact .... ?

It's a long piece of code, but here it is:

Private Sub cmdCopyForm_Click()
On Error GoTo ErrHandler

'Check for Unsaved Data
If ReqdFields = True Then Exit Sub
If Me.Dirty = True Then Me.Dirty = False

'Define variables
Dim strName As String
Dim lNewContactID As Long
Dim lCheckClientID As Long
Dim lOldClientID As Long: lOldClientID = Me.ClientID
Dim ctl As Control
Dim i As Integer: i = 1
Dim aValue(1 To 100) As Variant
Dim strSQL As String
Dim lngYellow As Long: lngYellow = RGB(255, 255, 0)
Dim Ans As Integer

'Exit if no contact chosen
If IsNull(Me.cboCopyID) Then Exit Sub

'Check that record doesn't already exist
lNewContactID = Me.cboCopyID.Value
lCheckClientID = Nz(DLookup("ClientID", "tblClient", "ContactID = " & lNewContactID), 0)
If lCheckClientID <> 0 Then
MsgBox "Cannot copy record at this time; contact is already registered as a client."
Exit Sub
End If

'Confirm
strName = Nz(DLookup("FullName", "tblContact", "ContactID = " & lNewContactID), "None")
If MsgBox("Are you sure you want to copy the record for this contact?: " & strName, vbYesNo, "CONFIRM CONTACT") = vbNo Then
Me.cboCopyID.Value = Null
Exit Sub
End If

'Check if FamilyID should be copied
If Not IsNull(Me.numFamilyID) Then
Ans = MsgBox("Do you want to copy the FamilyID? Last chance to Cancel!", vbYesNoCancel, "COPY?")
Select Case Ans
Case vbNo
Me.numFamilyID.Tag = "Skip"
Case vbYes
Me.numFamilyID.Tag = "Reqd"
Case vbCancel
Me.cboCopyID.Value = Null
Exit Sub
End Select
End If

'Copy all relevant fields in current record
For Each ctl In Me.Controls
If ctl.Tag <> "Skip" And (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox) Then
aValue(i) = ctl.Value: i = i + 1
End If
Next ctl

'Reset Counter
i = 1
Set ctl = Nothing

'Add new record: set Foreign Key
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
Me.ContactID = lNewContactID


'Paste main form records
For Each ctl In Me.Controls
If ctl.Tag <> "Skip" And (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox) Then
ctl.Value = aValue(i): i = i + 1
End If
Next ctl

'Check whether to copy related records
If MsgBox("Do you want to copy the immigration history?", vbYesNo, "COPY?") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
strSQL = "INSERT INTO [tblImmHistory] ( ClientID, ImmStatus, ImmCode, ImmNote, ImmStart, ImmEnd ) " & _
"SELECT " & Me.ClientID & " as ClientID, ImmStatus, ImmCode, ImmNote, ImmStart, ImmEnd " & _
"FROM [tblImmHistory] WHERE ClientID = " & lOldClientID & ";"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.subImmHistory.Requery
Else
Me.subImmHistory.Form.AllowAdditions = True
End If


'Confirm successful copy
MsgBox "The record has been copied. Please Note that you must still enter information for this client including " & _
"Date of Birth, Immigration# / Type, Permission for Funder to Contact, and Share Immigration Story.", vbOKOnly, "SUCCESSFUL"

'Highlight required fields
With Me
.txtDOB.BackColor = lngYellow
.cboImmNumberType.BackColor = lngYellow
.txtImmNumber.BackColor = lngYellow
End With

If Not IsNull(Me.FamilyID) Then
Me.cboFamilyRelation.BackColor = lngYellow
Me.txtPrimarymember.Visible = True
End If

'Reset
Me.AllowAdditions = False
Set ctl = Nothing
Me.cboCopyID.Value = Null
Me.Refresh
Me.txtFullName.SetFocus
Me.Copied = True

ExitHere:
Exit Sub

ErrHandler:
Call ErrHandlerRoutine
GoTo ExitHere



End Sub
Go to the top of the page
 
DanielPineault
post Nov 23 2017, 11:17 AM
Post#13


UtterAccess VIP
Posts: 5,452
Joined: 30-June 11



Hmmm.... very odd! Nothing is jumping out at me.

The only thing I might do differently is

Use db.Execute strSQL instead on DoCmd.RunSQL, then you don't need to disable warnings and reenable then, and you can check if it worked by doing something like
CODE
    Dim db                    As DAO.Database

'...

    'Check whether to copy related records
    If MsgBox("Do you want to copy the immigration history?", vbYesNo, "COPY?") = vbYes Then
        DoCmd.RunCommand acCmdSaveRecord
        '        DoCmd.SetWarnings False
        strSQL = "INSERT INTO [tblImmHistory] ( ClientID, ImmStatus, ImmCode, ImmNote, ImmStart, ImmEnd ) " & _
                 "SELECT " & Me.ClientID & " as ClientID, ImmStatus, ImmCode, ImmNote, ImmStart, ImmEnd " & _
                 "FROM [tblImmHistory] WHERE ClientID = " & lOldClientID & ";"
        Set db = CurrentDb
        db.Execute strSQL, dbFailOnError
        If db.RecordsAffected = 0 Then 'No records were inserted
            'It didn't work, why?
            '   Should we display a message to the user to let them know?
        Else 'The records were inserted
            Me.subImmHistory.Requery
        End If
    Else
        Me.subImmHistory.Form.AllowAdditions = True
    End If


and I tweak the Error handler very slightly to
CODE
ExitHere:
    On Error Resume Next
    Set ctl = Nothing
    Set db = Nothing
    Exit Sub


Then again, I doubt any of the above will actually address whatever the current issue is.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
lemming
post Nov 23 2017, 11:46 AM
Post#14



Posts: 150
Joined: 21-March 14



Thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:55 AM