ERROR 3075 - WHAT'S WRONG WITH THE SYNTAX?

[Angelique]

The following syntax is giving me an 3075 error message:

If Not IsNull(DLookup("ID", "Training Details"![ClientID]='" & MySet![ClientID] & "' AND TrainingDetails![Date of Training]=" & mySql![Date of Training]))

Through the forum I found a posting to handle the error which is great! BUT naturally it bombs on what I want it to do.

This is when I realised:

1. When the listbox entry is inserted into the temporary
table (although the word temporary isn't true as I
don't remove it) it removes it from the listbox so
prevent the user from adding to the subform the same
client. I would like to retain this!

2. But this means that the recordcount of the listbox is
Recordcount - 1 e.g. starts of at 78 and when the
user selects the client, reduces for each client.

3. The subform (datasheet) displays the newly added
entry into the tblClientSelect but this table
doesn't have the date of training copied and when
I tried to go that way, I had all sorts of problems.

4. The "process" command button runs the code to open
the required report with the currently selected
clients. This works exactly as I wanted it and
don't have a problem.

5. I also have in the "process" button the above syntax
so that when the user selects "process" it updates
the Training Details table so that next time the
user runs the "qryOverdue" it only picks up those
records that are overdue where the "OverdueLetter"
is blank. Thus preventing the user having to deal
with the same overdue clients each time.

What I need to know is how can I refresh the original recordcount for the listbox after the selection has been made which obviously removes it so that I can compare the ClientID and Date of Training with those records in the Training Details table so that I can insert a date for that particular client for that particular Date of Training?

Angelique

 

[tlbroadbent]

You have not specified a DOMAIN in the DLookup function call.

DLookup("ID", "Missing DOMAIN", "Training Details"![ClientID]='" & MySet![ClientID] & "' AND TrainingDetails![Date of Training]=" & mySql![Date of Training])

Rewrite it thus... if "Training Details" is the name of the table or query you want to search.

DLookup("ID", "Training Details", [ClientID]='" & MySet![ClientID] & "' AND [Date of Training]=#" & mySql![Date of Training]) & "#"

You have MySet and MySQL in the criteria. Is that intentional? Or should the references be identical? You may need to add # around the date criteria as I did. I'm not sure of your formats. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Aivars]

Hi, Angelique!

You was used incorrect identifiers for Date.

Text identifier >>> '
Date identifier >>> #

Accordingly:

If Not IsNull(DLookup("ID", "Training Details"![ClientID]='" & MySet![ClientID] & "' AND TrainingDetails![Date of Training]=#" & mySql![Date of Training])) & "#"

Aivars

 

[Angelique]

I didn't forget the domain except in the posting! my apologises.

Set MySet = MyDB.OpenRecordset("tblClientSelect", DB_OPEN_DYNASET)
Set MySQL = MyDB.OpenRecordset("qryOverdue&quot
Set MyTable = MyDB.OpenRecordset(tblClientLabel", DB_OPEN_DYNASET)
Set TrainingDetails = MyDB.OpenRecordset("Training Details&quot

The syntax is:

If Not IsNull(DLookup("[ID]", [Training Details]", "[ClientID]='" & MySet![ClientID] & "' AND TrainingDetails![Date of Training]=" & mySQL![Date of Training]))

I did modified last night the inserting of the [Date of Training] into MySet recordset with success except it inserts all entries from the query with the same client and not the selected client.

I did originally use the # identifier for a date but kept getting errors! I removed them and through the Bug Window it displayed a value with date format! I note that the # are placed slightly different to what I had so will try that.

I use the recordset MySet because I got a "Object Required" error message and since I had already declared the object table "tblClientSelect" it seemed logical to use it again.

I use the recordset mySQL because it didn't like the original syntax Me![lstClient].Column(2) as written in a previous posting.

Help would be always appreciated from such fanastic people (enough sucking up, I can give more!)


Angelique

 

[Aivars]

Hi, Angelique!

It's hard to look on your trouble...
Syntax of all DLookup(...) what you posted before is wrong.

If Not IsNull(DLookup("[ID]", "Training Details", "[ClientID]='" & MySet![ClientID] & "' AND "[Date of Training]=#" & mySQL![Date of Training] & "#")

Why don't you like the syntax Me![lstClient].Column(2)? It works fine always when I use it in my forms.

I think you can keep previous codes (original), and only repair its.

Also I think you don't need all sets on top of codes because you have all needed data for selection criterias on your form. Why do you want to complicate your work?

Good luck!
Aivars