Dlookup

[music_al]

Can someone please tell me what is wrong with this before I throw this laptop out of the window...

Cur_Candidate_Status = DLookup("Candidate_Status_Long", "qry_Candidate_Status2", "Candidate_ID = " & Forms![splitform_RRARs_Approved]![frm_Candidates].Form!txt_Candidate_ID)

The Form names are correct.

I've also tried...

Cur_Candidate_Status = DLookup("Candidate_Status_Long", "qry_Candidate_Status2", "Candidate_ID = " & Forms![splitform_RRARs_Approved]![frm_Candidates].Form![COLOR=red][[/COLOR]txt_Candidate_ID[COLOR=red]][/COLOR])

I think the split form is the cause of the issue.

 

[Ranman256]

use the BUILDER to validate that:
Forms![splitform_RRARs_Approved]![frm_Candidates].Form!txt_Candidate_ID

is the correct path.

 

[isladogs]

I would tell you but, if you've already thrown the laptop out of the window, you won't see my reply.

 

[music_al]

The builder gives me...

[Forms]![splitform_RRARs_Approved]![frm_Candidates].[Form]![txt_Candidate_ID]

still doesn't work

 

[music_al]

Please tell me isladogs

 

[music_al]

what is interesting is, this is a 2 part query to return the status related to a maximum date. The first part of the query is asking for the Candidate_ID even though the way I have built the criteria is correct.

I really think this is a problem with Split Forms.

 

[isladogs]

Please tell me isladogs

Is Candidate_ID text or number datatype?

 

[music_al]

Its a Number datatype

 

[Gasman]

Is that a subform you are getting the data from?

 

[Gasman]

FWIW I just created a form and added that splitform I used on your query question as a subform.

I used

MsgBox DLookup("Description", "Transactions", "ID = " & Forms![Form1]![ctlTransactions].Form.ID)

for a button on the main form and it shows the correct value for the ID on the splitform

Form1 is the main form, ctlTransactions is the control that holds the form spfTransactions.

I even tried similar code for a button on the split form and it did the same.?

So, split forms do have limits, but this is not one of them. Same applies to the queries.

 

[Minty]

What do you get if you

Debug.Print [Forms]![splitform_RRARs_Approved]![frm_Candidates].[Form]![txt_Candidate_ID]
?

Split forms are notoriously awkward to do anything vaguely clever with. Do you have to use a split form - can't a linked subform give you more flexibility?

 

[music_al]

In the end I deleted the Split Form and started again.

That seemed to fix the problem so all is good. A bit of a mystery.

 

[The_Doc_Man]

When you delete something and then rebuild it and it works, there is a significant chance that you were dealing with corruption of some sort. A full Compact & Repair is probably in order right now. Followed by making a backup copy just in case things go south again.