Code works only after running repeatedly

[Rob]

Hi,
I'm having issues with the following code that is run from a button on a
form, when first run no records are updated yet run a second time, records
are imported albeit not always every record, run several times, I get the
correct result. I'm guessing there is a fundamental coding error but can't
locate it.

Any pointers would be appreciated. Rob

Public Function ImportSales()

On Error GoTo ImportSales_Err



DoCmd.SetWarnings False

DoCmd.TransferDatabase acImport, "dBase IV", "N:\MBM\DBASE\IMPORTS",
acTable, "MBMSALE.DBF", "MBMSALE"

DoCmd.RunSQL "UPDATE salemain AS d1, MBMSALE AS d2 SET d1.GLASS =
d2.GLASS, d1.TOTGALL = d2.TOTGALL, d1.JUCVAL = d2.JUCVAL, d1.SEASPROD =
d2.SEASPROD, d1.TOTPROD = d2.TOTPROD, d1.TURNOVER = d2.TURNOVER WHERE
(((d2.ACCNO)=d1.ACCNO) And ((d2.WKEND)=d1.WKEND));"

DoCmd.RunSQL "INSERT INTO SALEMAIN ( ACCNO, WKEND, GLASS, TOTGALL,
JUCVAL, SEASPROD, TOTPROD, TURNOVER )SELECT DISTINCTROW MBMSALE.ACCNO,
MBMSALE.WKEND, MBMSALE.GLASS, MBMSALE.TOTGALL, MBMSALE.JUCVAL,
MBMSALE.SEASPROD, MBMSALE.TOTPROD, MBMSALE.TURNOVER FROM MBMSALE LEFT JOIN
SALEMAIN ON (MBMSALE.ACCNO = SALEMAIN.ACCNO) AND (MBMSALE.WKEND =
SALEMAIN.WKEND)WHERE (((SALEMAIN.ACCNO) Is Null) AND ((SALEMAIN.WKEND) Is
Null));"

DoCmd.DeleteObject acTable, "MBMSALE"

DoCmd.SetWarnings True



ImportSales_Exit:

Exit Function



ImportSales_Err:

MsgBox Error$

Resume ImportSales_Exit





End Function

 

[Allan Murphy]

Rob
Change Public Function ImportSales() to SUB ImportSales()

change End Function to End Sub

A function is used to return value a procedure processes commands etc.

 

[Rob]

Allan,

Thanks for prompt reply. I've made changes by still needs to be run more
than once to import records.

Does the code look correct otherwise?

Thanks, Rob

 

[Tom Wickerath]

Allen,

While your statements are certainly true about functions being used to
return a value whereas subroutines process commands, this would not cause a
procedure to fail. In some cases, a procedure must be declared as a function
(for example, if you need to call your procedure from a macro, it must be a
function, not a subroutine).


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi Rob,

Instead of using DoCmd.SetWarnings False, try using CurrentDB.Execute with
the optional dbFailOnError parameter. You will need to have a reference set
to the DAO Object library in order to include dbFailOnError. This should help
you to identify a problem in one of the queries, which might otherwise be
hidden by having the warnings turned off. Something like this:


Public Function ImportSales()
On Error GoTo ImportSales_Err

Dim strSQL as String

DoCmd.TransferDatabase acImport, "dBase IV", _
"N:\MBM\DBASE\IMPORTS", acTable, "MBMSALE.DBF", "MBMSALE"

strSQL = "UPDATE salemain AS d1, MBMSALE AS d2 SET d1.GLASS =
d2.GLASS, d1.TOTGALL = d2.TOTGALL, d1.JUCVAL = d2.JUCVAL, d1.SEASPROD =
d2.SEASPROD, d1.TOTPROD = d2.TOTPROD, d1.TURNOVER = d2.TURNOVER WHERE
(((d2.ACCNO)=d1.ACCNO) And ((d2.WKEND)=d1.WKEND));"

CurrentDB.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO SALEMAIN ( ACCNO, WKEND, GLASS, TOTGALL,
JUCVAL, SEASPROD, TOTPROD, TURNOVER )SELECT DISTINCTROW MBMSALE.ACCNO,
MBMSALE.WKEND, MBMSALE.GLASS, MBMSALE.TOTGALL, MBMSALE.JUCVAL,
MBMSALE.SEASPROD, MBMSALE.TOTPROD, MBMSALE.TURNOVER FROM MBMSALE LEFT JOIN
SALEMAIN ON (MBMSALE.ACCNO = SALEMAIN.ACCNO) AND (MBMSALE.WKEND =
SALEMAIN.WKEND)WHERE (((SALEMAIN.ACCNO) Is Null) AND ((SALEMAIN.WKEND) Is
Null));"

CurrentDB.Execute strSQL, dbFailOnError

DoCmd.DeleteObject acTable, "MBMSALE"


ImportSales_Exit:
Exit Function
ImportSales_Err:
MsgBox Error$
Resume ImportSales_Exit
End Function



Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Rob]

Tom,

Using your example looks to have done the job. You said I'd need a
reference to the DAO Object library which I've not specifically done but it
works - should I do anything to set up a reference or is this done by
entering your example code?

Many thanks, Rob

 

[Tom Wickerath]

Hi Rob,

You would have received a compile error if you did not have a reference set
to the "Microsoft DAO 3.x Object Library", where x = 51 for Access 97 (ie.
3.51) and x = 6 for Access 2000, 2002 and 2003 (ie. 3.6).

If you created this database using Access 2003, then you already have this
reference set by default. The same is not true for new databases created
using either Access 2000 or 2002.

To view your references, open any code module. Then click on Tools >
References... You should see a reference set to this library.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Rob]

Tom,

Thanks for the reply which has been very useful and most informative. I
much appreciate your input, Rob