Records changing unexpectedly

[Vanguard]

I have been having a problem with records being changed in my database after running a macro.

The DB consists of a clients table and a projects table. It happens after running certain macros from the project form (see first picture below) When I run say the "create design cert" macro the record will display the pencil icon to show it has been edited but the macro should only be reading from the records.

It isn't until the form is closed and opened again that the changes display. It is common among all my macros that open word or excel to import project and client info into a document. Ones that say just open the job folder dont cause the problem.

Also it is just the suburb and Stare fields of the projects table that are changed to the values of the suburb and state values listed on client table for that job.

There is a one to many relationship between the ClientID on the client table and the Client field on the project table.

16203

16204

16202

Below is the code for the "create design cert" macro. Is there anything obvious in VBA here that is wrong and could be causing the unwanted record edit? I'm self taught in my spare time so apologies in advance.


Private Sub FillWordCertDesign_Click()

Dim appword As Word.Application
Dim doc As Word.Document
Dim Path As String

On Error Resume Next
Error.Clear
Path = Me.Path & "\Certifications\Design Certification Letter.docx"
Set appword = GetObject(, "word.application")
If Err.Number <> 0 Then
Set appword = New Word.Application
appword.Visible = True
End If

FirstName = DLookup("[First Name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
LastName = DLookup("[Last name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
Company = DLookup("[Company Name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
StreetAddress = DLookup("[StreetAddress]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
Suburb = DLookup("[Suburb]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
State = DLookup("[State]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
PostCode = DLookup("[PostCode]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")

Set doc = appword.Documents.Open(Path, , True)
With doc
.FormFields("txtJobNumber").Result = Me.Job_Number
.FormFields("txtAttn").Result = FirstName & " " & LastName
.FormFields("txtCompany").Result = Company
.FormFields("txtStreetAddress").Result = StreetAddress
.FormFields("txtSuburb").Result = Suburb
.FormFields("txtState").Result = State
.FormFields("txtPostCode").Result = PostCode
.FormFields("txtProjectName").Result = Me.Project_Name
End With
appword.Visible = True
appword.Activate

appword.ActiveWindow.View.Type = wdPrintView

Set doc = Nothing
Set appword = Nothing

End Sub

Any help or insights would be great. This has been driving me nuts for quite a while now.

[jonh]

Comment the code out and check if it still does it.

Where do the drop downs get their data from?

[Vanguard]

Thanks for the reply jonh.

I commented out from the second line of the code to the second last line and it fixed the issue of the record being edited when the macro is run. I guess that means that it is something in the code that is doing the editing?

The combo boxes for the suburb and state are populated with any suburbs or states that have already been entered into the projects table. This is done via a row source query in the combo's properties as seen below.

16212

[jonh]

Nothing stands out in the code that would edit the record.

If it's multi user, test on a local copy.

After commenting out code, add parts of the code back in until you find the part that causes the problem.

You could upload a test version of your db, but the latest version I have is 2010 and I guess you are on 2013.

[jonh]

Well, I guess it was so obvious I couldn't see it...


FirstName = DLookup("[First Name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
LastName = DLookup("[Last name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
Company = DLookup("[Company Name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
StreetAddress = DLookup("[StreetAddress]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
Suburb = DLookup("[Suburb]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
State = DLookup("[State]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
PostCode = DLookup("[PostCode]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")


You probably intended these to be variables, but some of them are names of fields / controls. So you need to rename them.

[Vanguard]

Spot on!

I just tested renaming the State and Suburb variables and it worked like a charm.

I didn't even know you could overwrite records that simply.

Thanks for all your help.