Web Development

Ask something
Question Locked

Generating Codes with Update Query

0 helpful
5 replies

by jim

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi all,
I am generating a multi source concatenated code with an update query in Microsoft Access. I am wondering if there is a way to cut the update to only new entries rather than updating all records each time the query is run.
Any helpers for this problem?

5 replies

by tony hopkinson

Update MyTable Set MyCodefield = ....
Where MyCodeField is Null

Given of course on a new record it is null.
Otherwise add another column integer default to 0 is nice.

Update MyTable Set MyCodefield = ...., MyFlagField = 1
Where MyFlagField = 0;

The nice thing about the int is you can 'cascade' the update.
0 - 1 does this
1 - 2 does this etc.

by jim

Thanks Tony.
I understand what you are telling me to do, i think.
I want to show you the code that i have, maybe you can use it to help me alittle more.

Private Sub cmdGenCode_Click()
On Error GoTo Err_cmdGenCode_Click

Dim stDocName As String

stDocName = "Code Generatore"
DoCmd.RunMacro stDocName

Exit_cmdGenCode_Click:
Exit Sub

Err_cmdGenCode_Click:
MsgBox Err.Description
Resume Exit_cmdGenCode_Click

End Sub

That Macro runs this Query:

UPDATE (StrainCodes INNER JOIN InoculationRecord ON StrainCodes.[Strain ID] = InoculationRecord.Strain) INNER JOIN spawntransferunits ON InoculationRecord.TransID = spawntransferunits.inoculationid SET spawntransferunits.MasterCode = Format([seeddate],"mmddyy") & "-" & [species code] & "-" & [strain code] & "-" & Right("00" & [unitlotnum],2) & [transvessel];

So Do I just add the where clause to the update query, WHERE MasterCode = 0? (Mastercode is my default 0 field before updating)

by jim

Great work, Tony. Take five. :)

This discussion is closed to new replies.