Problem Referring to Table Field

[Cosmonaut_99]

Hi,

I'm trying to run an if statment, on the records in a field (called "Current_Month"), in an existing table called ""Current_Months_Lag1_Data".

The IF statements work fine - and simply perform a different action for different data in "Current_Month".

I am having problems referring to "Current_Month", where I keep getting 424 (Object required) and 3420 (Object invalid) error messages, on the last line of code.

I'm stumped as to how to resolved this, (I have defined & identified the table in which the field is located, yet somehow cannot identify the field within that table).

(I've already tried searching the web for similar problems under error messages 424 & 3420).

I'd appreciate any suggested solutions.

------------------------------------------------------------------
Function LAG_Forecast_03()

Dim dbs As Database
Set dbs = CurrentDb
Dim Tbl As TableDef
Set Tbl = CurrentDb.TableDefs("Current_Months_Lag1_Data")
Dim FLD As Field
Set FLD = Tbl![Current_Month]

 

[JHB]

Try using dbs instead of CurrentDb:

Dim dbs As Database
Set dbs = CurrentDb
 
Set Tbl = dbs.TableDefs("Current_Months_Lag1_Data")

 

[Cosmonaut_99]

Thanks - I've now tried that change, but when I run the code, I now get a 424 (Object Required) error - on that line of the code.

 

[JHB]

Which references do the database have?
Else post the database with some sample data?

 

[Cosmonaut_99]

Hi,

Please see attached a lite version of what I am trying to do.

In essence Table "Lag 1 EMEA Forecasts" is populated from 12 separate ODBC source tables connected to the database - in the Lite version there are no ODBC links.. as this works fine already.

I then want to use today's date, to lookup a 3 character month reference(which will relate to a financial calendar - not a normal calendar) - and populate this into table "Current_Months_Lag1_Data".

Then in the 2nd module, (where the problem occurs), I want to populate data from table "Lag 1 EMEA Forecasts" into table "Current_Months_Lag1_Data", based on the IF statements successfully matching the 3 character month reference already specified.

The problem concerns the definitions and settings for the "Current_Months_Lag1_Data" table and field at the start of the 2nd module.

Cheers.
C99.

 

[JHB]

You can't use The Fields collections to get a value from a field, then you have to use a recordset.

Here is one example to do it then:

Function LAG_Forecast_03()
  Dim dbs As Database, rst As Recordset
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Current_Months_Lag1_Data")
  If rst![Current_Month] = "APR" Then
    DoCmd.RunSQL "UPDATE Current_Months_Lag1_Data INNER JOIN [Lag 1 EMEA Forecasts] ON Current_Months_Lag1_Data.Item = [Lag 1 EMEA Forecasts].Item " & vbCrLf & _
    "SET Current_Months_Lag1_Data.Lag1_Month1 = [Lag 1 EMEA Forecasts]![MAR_LAG1],  " & vbCrLf & _
    "Current_Months_Lag1_Data.Lag1_Month2 = [Lag 1 EMEA Forecasts]![MAR_LAG2],  " & vbCrLf & _
    "Current_Months_Lag1_Data.Lag1_Month3 = [Lag 1 EMEA Forecasts]![MAR_LAG3];"
  ElseIf rst![Current_Month] = "OCT" Then
    DoCmd.RunSQL "UPDATE Current_Months_Lag1_Data INNER JOIN [Lag 1 EMEA Forecasts] ON Current_Months_Lag1_Data.Item = [Lag 1 EMEA Forecasts].Item " & vbCrLf & _
    "SET Current_Months_Lag1_Data.Lag1_Month1 = [Lag 1 EMEA Forecasts]![MAR_LAG1],  " & vbCrLf & _
    "Current_Months_Lag1_Data.Lag1_Month2 = [Lag 1 EMEA Forecasts]![MAR_LAG2],  " & vbCrLf & _
    "Current_Months_Lag1_Data.Lag1_Month3 = [Lag 1 EMEA Forecasts]![MAR_LAG3];"
  End If
End Function

 

[Cosmonaut_99]

Thanks - that explains why I couldn't get it to work.

I'll use the recordset suggestion.