search remote table

[dodyryda]

Hi .. I'm trying to do something pretty basic (I think) and just wondering if anyone can help me..

I wish to search a remote table (holidays) that contains a list of dates for a date value that is given on my current form.. if its there I wish to lock the subform I have for editing.. but I'm getting object required errors.. would anyone have some suggestions?

Dim rs2 As ADODB.Recordset
Set rs2 = Me.RecordsetClone
rs2.Find "[Job Date] = #" & Format(Holidays![Holiday Dates], "dd/mm/yyyy") & "#"
If rs2.BOF Or rs2.EOF Then
Me.EditJobsfrm.Locked = False
Else
Me.EditJobsfrm.Locked = True
End If

 

[Guus2005]

Using the Holidays table from another Access database:

Select * from Holidays IN 'C:\Temp\Holiday_be.mdb'

Share and Enjoy!

 

[dodyryda]

hi thanks for the response.. the holiday table is in the same db tho

Dim rs2 As ADODB.Recordset
Set rs2 = Me.Recordset.Clone
rs2.MoveFirst
rs2.Find "Holidays![Holiday Dates] = [Job Date]"
If rs2.BOF Or rs2.EOF Then
Me.EditJobsfrm.Locked = False
Else
Me.EditJobsfrm.Locked = True
End If

red line kicks error about arguments are of the wrong type, out of acceptable range / conflict.. both are short dates sorry should have said this is running off a forms Onupdate event procedure

 

[Guus2005]

Did you add msado15.dll to your references? (Microsoft ActiveX Data Objects)
To use ADO you need the ADO library.

 

[Guus2005]

Oke, that's not it then.

You are formatting Holidays![Holiday Dates] and not [Job Date]. Format the latter and try again.

HTH

 

[dodyryda]

this is an access adp project, and I already have other ado procedures working so figure this isn't necessary

re the format i removed this as it wasn't necessary so just have rs2.Find "[Job Date] = Holidays![Holiday Dates]" still the arguments error

 

[LPurvis]

I wish I could say this was down to my pet hate (which is the use of Bangs in SQL - which is a poor practice in JetSQL - and an impossible one in T-SQL ;-).

Alas it's a couple of issues. First delimitation was required - and then you had an problem due to the table qualification.
You're using the Find method - so the table prefix is not only unnecessary, but causing a reference problem as that isn't how the field is named once within the form's recordset.
The delimitation is simple too. Just use a single quote instead of octothorpe.
But you must format the date appropriately.

rs2.Find "[Job Date] = '" & Format(dtmDate, "mm/dd/yyyy") & "'"or
rs2.Find "[Job Date] = '" & Format(dtmDate, "yyyy-mm-dd") & "'"


So then you're just left with determining the dtmDate value.
You say it's from an external (not remote) table.
Is there only one row in this table?
How do you know which one to return.
The reference Holidays![Holiday Dates] is often attempted - but even if such table evaluation were possible (it's not), how would it know which row to return?

Cheers.

 

[dfenton]

A form's .RecordsetClone property returns a DAO recordset, so you have to use FindFirst instead of Find (Find exists only in ADO recordsets).

There is very seldom any justification at all for using ADO in Access. Is there some compelling reason why you're using it?

 

[LPurvis]

Fairly compelling, it's an ADP (mentioned once about halfway through the thread).
Like 'em or loathe 'em - there they are, ADO and all.

Cheers.

 

[dodyryda]

leigh

how can i get the dtmDate value, I'm trying to find a record in my table holidays [holiday dates] that equals the job date field on my form

 

[LPurvis]

In theory it's trivial. It just depends upon how involved you want to make this.

Fetching the value, you could just go with:
dtmDate = CurrentProject.Connection.Execute("SELECT [HolDate] FROM [tblHolDates] WHERE [JobDate] = '" & Format(Me.JobDate, "yyyy-mm-dd") & "'")(0)

A more robust request / properly error handled would be important though (there might not be a match).
That effort can be obfuscated by using a simpler method such as DLookup:
dtmDate = Nz(DLookup("[HolDate]", "[tblHolDates]", "[JobDate] = '" & Format(Me.JobDate, "yyyy-mm-dd") & "'"), 0)

Either way, it's a separate fetch from the database, but joining the form source isn't going to be a particuarly more efficient choice - especially if you're applying updates to the form then as singular a source as possible is best (i.e. leaving it based on one a one table data source).

Cheers.

 

[dodyryda]

nice one , thanks leigh,, I'll give it a shot

 

[dodyryda]

I'm getting an error on the highlighted field as this in a different table called Jobspec not the holidays table

dtmDate = Nz(DLookup("[Holiday Dates]", "[Holidays]", "[Job Date] = '" & Format(Me![Job Date], "dd/mm/yyyy") & "'"), 0)

 

[Guus2005]

Two remarks:
Dlookup returns the result of a single field from a query or table where a matches the criteria you add. So if you want to retrieve some value from another table you have to change the name of the table

dtmDate = Nz(DLookup("[Holiday Dates]", "[Holidays]", "[Job Date] = '" & Format(Me![Job Date], "dd/mm/yyyy") & "'"), 0) 

must be

dtmDate = Nz(DLookup("[<Enter the fieldname>]", "[Jobspec]", "[Add the where clause field] = '" & Format(Me![Job Date], "dd/mm/yyyy") & "'"), 0)

Secondly,

dtmDate = Nz(DLookup("[Holiday Dates]", "[Holidays]", "[Job Date] = '" & Format(Me![Job Date], "dd/mm/yyyy") & "'"), 0)

when dtmDate is of the type Date and the Dlookup function returns NULL, the Nz function returns 0 which is then converted to the matching date: 30/12/1899.

It is up to you if that is a valid date.

Enjoy!

 

[LPurvis]

The DLookup code I offered was, naturally, aircode without knowing your object names.
However the suggested format was definitely not.

dtmDate = Nz(DLookup("[HolDate]", "[tblHolDates]", "[JobDate] = '" & Format(Me.JobDate, "yyyy-mm-dd") & "'"), 0)

It's vital that this is how you format the date.
Or at least not in the format you have (U.S. style is the alternative).

Cheers.

 

[dodyryda]

thanks chaps I'll give this a shot..

 

[dodyryda]

Thanks for your help guys.. sorry for delay getting back to you.. your suggestions worked a treat..

One last thing I'm slightly confused about that you may know about as I have this little search linked to the Onclick event of the calendar is the dtmDate value seems to be running before the calendar updates it's current value. So when I click a 'new' date the dtmDate value in debug is collecting the previous value.

 

[LPurvis]

You'd have to show the code in question really.

 

[dodyryda]

many thanks for the help chaps... worked a treat.!

 

[LPurvis]

Oh, OK then. That was easy. Cheers.