why would this happen?

[Seb]

why would this occur???

this is my query:
strSQL = "INSERT INTO tblRunItems ( SiteId, RunID )SELECT tblSites.SiteId, " & Me.RunId & " FROM tblSites, tblRuns WHERE tblSites.CollectionZone = '" & Me.RunZone & "' AND tblSites.CollectionDay = '" & Me.RunDay & "'"

This results in the same records being entered 4 times into tblRunItems

Cant for the life of me figure out why

 

[FoFa]

You are not joining tblSites to tblRuns, so it is creating rows in tblSites * rows in tblRuns that meet your criteria (or something like that).

 

[Seb]

Thanks....did what you said and it worked....except when the tblsites has blanks is it:

this is what I have thus far:

INSERT INTO tblRunItems ( SiteId, SharedSite, RunId )
SELECT tblSites.SiteId, tblSites.SharedSite, tblRuns.runID
FROM tblRuns INNER JOIN tblSites ON (tblRuns.RunZone = tblSites.CollectionZone) AND (tblRuns.RunDay = tblSites.CollectionDay)
WHERE (((tblRuns.runID)=[forms].[frmRuns].[runId]));

what do you think?

 

[neileg]

If you want to return all records from tblruns even when there is no matching record in tblsites, you need to change the inner join to a left join.

 

[Seb]

Even when frmRun.runZone = "" (so its blank) and there are matching records in tblSites (where the zone is blank)
should this not pick it up? not quite sure, but I think I'll just put some error handling into it forcing them to enter some info....

Thanks again guys

 

[FoFa]

NOTE - Blanks and NULLs do NOT match. So if it is blank (or it thinks it is) on your form, but NULL in your table, it will not match.