Try the changed code below. It looks like the problem is you are enclosing
the entire line in quotes, so SQL sees it as a literal. Also, the table name
needs to be in quotes which is CHR(34). Since I haven't tested it, It could
be almost
correct.
:
Cool thanks think we are nearly there
. Just having a problem with the
table name reference, code produces error 'cannot find input table or query
'strCountryTable' ?
Select Case strCountry
Case Is = "E"
New strCountryTable = chr(34) & "Dealer Selection Fr" & chr(34)
Old DB.Execute "DELETE FROM " & strCountryTable, dbFailOnError"
New DB.Execute "DELETE FROM strCountryTable, dbFailOnError"
strCountryForm = "frm Dealer Selection France"
Set dbOther = OpenDatabase("S:\NMGB\Warranty Data\Nissan Warranty
Data 2000.mdb")
dbOther.Execute ("Dealer Selection Extract"), dbFailOnError
End Select
--
Thanks in advance for any help.
Sue
:
Okay, I am still a little lost. Pardon me for being so thick. I understand
that you need two values to pass, one for the country code, and one for the
date. Is that correct? If the function that does the date check and has to
look at the two parameters, then you can pass them both together.
TakeAHoliday(Me.cmdCountryE, WhateverDate)
If the table names are different for each country, then the country code
could be used to select the table name:
Sub Take a Holiday(strCountry as String, dtmCheckDate as Date)
Dim strCountryTable as String
Select Case strCountry
Case is = "E"
strCountryTable = "tblElbonia"
Case is "Fr"
strCountryTable = "tblFrance"
...
...
End Select
Then use strCountryTable to open the recordset.
I certaintly hope I am getting closer to understanding.
:
Hi, sorry if I am not making myself clear enough! Thanks for trying to help,
I will try to explain it a bit better...
I have approximately six different countries to deal with, each has its own
database which is used by my database to pull information from. Currently the
user would click a country button on my selection form, this runs an execute
delete to empty records from the Country table (eg Dealer Selection Fr - one
for each country) then an append query (from the external country db) which
repopulates the country table in my database. That is stage one, this code
for each country is the same except obviously the name of the tables and
external db.
Stage two, if there is a date in the Next audit field, the system checks to
see if it is a 'working' day and if not updates it to the next working day,
excluding weekends and holidays (using a holiday table which has to look up
country and date for a match). This is applied to the whole recordset just
created for the country table and is a public function called within the code
that is attached to the onclick event. The code is the same except for the
countrycode. I want a way of capturing the country in a variable to use this
instead of hard coding the letter as below:
Set rstHols = DB.OpenRecordset("Select [HolidayDate] from tblHolidays where
[CountryCode]= forms!['E'", dbOpenSnapshot)
Are you totally confused now ? ;-)
Thanks in advance for any help.
Sue
:
Pass 2 parameters where? I guess I don't understand the problems fully. I
will read over some more and see if I can get a better understanding.
Sorry to hear you are ill. Hope you are feeling better soon. Post back
Monday if you are feeling better with more info if you can.
:
Ok thanks, will try it out but a little confused (doesn't take much ;-))... I
need to pass the date parameter in to use this to find the next working day,
is it possible to pass two values or am I missing something here? Good point
re combo box
Sorry... home ill at mo so mind not functioning great,
that's my excuse anyway! Can't try this now til Monday...
--
Thanks in advance for any help.
Sue
:
Okay, then what I sent should work okay. I do have one question. Why are
you using a command button for each country rather than one combo box to
select a country?
:
Thanks for the prompt response. Yes you are correct in your assumptions, the
command buttons for all the countries exist on the same form. They run some
other code first though which then calls the holiday function (nextworkday):
Private Sub French_Dealers_Click()
On Error GoTo Err_DG_Dealers_Click
Dim DB As Database
Dim dbOther As Database
Dim rst As Recordset
Set DB = CurrentDb()
DoCmd.OpenForm "frmProgress"
DoEvents
DB.Execute "DELETE FROM [Dealer Selection Fr]", dbFailOnError
' DoCmd.SetWarnings False
Set dbOther = OpenDatabase("S:\Nissan France\Systems and Data\Nissan France
Warranty Data.mdb")
dbOther.Execute ("Dealer Selection Extract"), dbFailOnError
' DoCmd.SetWarnings True
DoCmd.Close acForm, "frmProgress"
Set rst = CurrentDb.OpenRecordset("select * from [Dealer Selection Fr]")
Do While rst.EOF = False
If Not IsNull(rst![Next Audit]) Then
rst.Edit
rst![Next Audit] = NextWorkDay(rst![Next Audit])
rst.Update
End If
rst.MoveNext
Loop
rst.Close
DoCmd.OpenForm "frm Dealer Selection France"
Set rst = Nothing
Set DB = Nothing
Set dbOther = Nothing
--
Thanks in advance for any help.
Sue
:
I had a little trouble understanding your question, but I think what you said
is that you click a command button to execute the code you posted. There are
multiple buttons, one button for each country. Am I correct so far? What I
could not tell for sure is whether all occurances of calling this code are on
the same form. In any case, you may consider move this code to a Module as a
function. Then, regardless of which form or button you use, you can call the
code. For Example, lets say you have a form called MyFirstForm with a command
button called cmdCountryE. In the On Click event of cmdCountryE you would
call it like
TakeAHoliday(Me.cmdCountryE)
Then your function would be
Function TakeAHoliday(strCountry as String) as Boolean
Set rstHols = DB.OpenRecordset("Select [HolidayDate] from tblHolidays
where [CountryCode]= " & Chr(34) & strCountry & chr(34), dbOpenSnapshot)
You will probably have to twike the syntax in the Where phrase, I always get
that wrong the first... second... Oh, Hell... a number of times before I get
it right.
:
Hi,
I have a piece of code that can be called by numerous different pieces of
code. The only difference is the Country. I know I can repeat the code and
change the countrycode manually (E is hardcoded in the example below) but is
there a way to get Access to detect which country it is called from and enter
this as a variable? The code is triggered from individual country buttons on
a form who's caption label/name can be set to the countrycode if this can be
used?
Set rstHols = DB.OpenRecordset("Select [HolidayDate] from tblHolidays where
[CountryCode]='E'", dbOpenSnapshot)
Thanks in advance for any help.
Sue
