syntax error converting tag property to control source.

How are you trying to assign the string in the Tag property to the ControlSource property?

@Norie,

    Me.TextCountOfPN.ControlSource = Me.TextCountOfPN.Tag
   ' Me.Repaint

Select allOpen in new window

@Pat,

The DLookup() had a lot of errors.  That is why it isn't working.

Not sure what you changed, but yours its not working either.

What is the point of keeping this in the tag property?  It would be better to just populate the unbound control in the Form's Current Event or some other place

This is a continuous form and needs to be displayed in all rows.

Thanks,
Ben

Not sure what you changed, but yours its not working either.

It is pretty much impossible to catch typos when you don't have the application to test with and I do not have time to create a test bed to fix your code.

Compare my suggestion to what you had.  You will see that you had embedded field references which simply will not work.  I attempted to separate them so that Access could properly substitute the values.

This is a continuous form and needs to be displayed in all rows.

You do know that unbound controls on continuous forms will show identical values on ALL rows, don't you?

Modify your form's RecordSource query to join to Progress_Note_GroupBy_VisitDate_Qry on the four fields and pick up CountOfID that way.  Use a LEFT join if there will not always be a matching record in the query.

@bfuchs,

The first thing I would do is confirm the data type of fields [Nurse_User_ID_Num_pn] and [ShiftFromHour].  Do this in the table that those fields come from and look at the table definition, not at the table itself.  What I find is that many people use the "lookup" feature of Access table design which makes it look like the field is text, when it is actually numeric.

I'm guessing that what you want is:

'if these two fields are numeric

=dlookup("CountOfID","Progress_Note_GroupBy_VisitDate_Qry", "VisitDate = #" & [visit_date] & "# and [Nurse_User_ID_num_pn] = " & [Nurse_User_ID_num_snv] & " And [ShiftFromHour] = " & [ShiftFromHour])

Select allOpen in new window

'if these nurse is string and hour is numeric

=dlookup("CountOfID","Progress_Note_GroupBy_VisitDate_Qry", "VisitDate = #" & [visit_date] & "# and [Nurse_User_ID_num_pn] = '" & [Nurse_User_ID_num_snv] & "' And [ShiftFromHour] = " & [ShiftFromHour])

Select allOpen in new window

'if both are strings

=dlookup("CountOfID","Progress_Note_GroupBy_VisitDate_Qry", "VisitDate = #" & [visit_date] & "# and [Nurse_User_ID_num_pn] = '" & [Nurse_User_ID_num_snv] & "' And [ShiftFromHour] = '" & [ShiftFromHour] & "'")

Select allOpen in new window

Hi Experts,

@Pat,

Modify your form's RecordSource query to join to Progress_Note_GroupBy_VisitDate_Qry on the four fields and pick up CountOfID that way.  Use a LEFT join if there will not always be a matching record in the query.

two issues with that.
1- forms record source needs to be editable.
2- it will likely be more code involved by changing record source of form back and forth by code.

Would prefer just to assign the dlookup to the particular text box record source when users clicks on a button.

You do know that unbound controls on continuous forms will show identical values on ALL rows, don't you?

I beleive its possible to have dlookup run on each record separately in continuous forms, have done this already many times.

@Dale,

'if these nurse is string and hour is numeric

This is the case, however your code also produces an #Name?

Thanks,
Ben

Pat,  

look at the source in his dlookup, it is a query that appears to have a group by clause (at least the name would imply that), which, if joined in the record source would most likely make it not updateable.

@Ben,

Did you check the data types of all the fields (in the source tables, not the query)?  If so, what were they?

Changing the RecordSource to a query with a left join will NOT make it not updateable unless there is something else at play here.

Well it did,
I had the following updatable query.

SELECT Skilled_Nursing_Visit_Note.*, Int([Visit_Date]) AS IntVisitDate, SNV_Printed_History.PrintedDate, SNV_Printed_History.PrintedBy, SNV_Printed_History.ID AS PrintedHistoryID, SNV_Printed_History.ReviewedDate, SNV_Printed_History.ReviewedBy, SNV_Printed_History.VendorsID, Mid([Client_Last_Name],1,1) AS L, Mid([Client_First_Name],1,1) AS F, DSum("CountOfNotes","SNVNotesQry","SNV_ID = '" & Skilled_Nursing_Visit_Note.SNV_ID & "'") AS CountOfNotes, Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0))) AS Duration1, (Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))/120)-nz(DSum("CountOfNotes","SNVNotesQry","SNV_ID = '" & Skilled_Nursing_Visit_Note.SNV_ID & "'"),0) AS MissingNotes, Int(Replace(Skilled_Nursing_Visit_Note.SNV_ID,"snv","")) AS Expr1, SNV_Printed_History.NoPrint, TimeValue(CDate(TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)-TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0)+1))*24*60 AS Duration
FROM Skilled_Nursing_Visit_Note INNER JOIN SNV_Printed_History ON Skilled_Nursing_Visit_Note.SNV_ID = SNV_Printed_History.SNV_ID
ORDER BY Int(Replace(Skilled_Nursing_Visit_Note.SNV_ID,"snv",""));

Select allOpen in new window

Now I have the follwing non updatable query.

SELECT Skilled_Nursing_Visit_Note.*, Int([Visit_Date]) AS IntVisitDate, SNV_Printed_History.PrintedDate, SNV_Printed_History.PrintedBy, SNV_Printed_History.ID AS PrintedHistoryID, SNV_Printed_History.ReviewedDate, SNV_Printed_History.ReviewedBy, SNV_Printed_History.VendorsID, Mid([Client_Last_Name],1,1) AS L, Mid([Client_First_Name],1,1) AS F, DSum("CountOfNotes","SNVNotesQry","SNV_ID = '" & Skilled_Nursing_Visit_Note.SNV_ID & "'") AS CountOfNotes
FROM (Skilled_Nursing_Visit_Note INNER JOIN SNV_Printed_History ON Skilled_Nursing_Visit_Note.SNV_ID = SNV_Printed_History.SNV_ID) LEFT JOIN Progress_Note_GroupBy_VisitDate_Qry ON SNV_Printed_History.VisitDate = Progress_Note_GroupBy_VisitDate_Qry.VisitDate;

Select allOpen in new window

You can prefer what you want but unbound controls  will show the same value on every row.

Not so sure about that with dlookup in question..

@Dale,
Visit_Date is datetime,
Nurse_User_ID_num_pn and Nurse_User_ID_num_snv are text
Shift_From_Hour is an integer.

Thanks,
Ben

Maybe is a good idea to share with us a screenshot of your form to better understand what your trying to accomplish...maybe there is another way of doing what you want

@John,
If that will solve the issue I gladly do it-:)
See attached.
Thanks,
Ben
Untitled.png

And the issue is ???? can you give an example...some data are needed...just blur the sensitive data

Well as posted above, I need to include the results of a dlookup in a text box on the detail section of the form.
however would like that to be done only upon user clicking on a button as not to cause unnecessary slowness.

Thanks,
Ben

and what exactly is your error...?
At first have you tested the Dlookup to see if it works as it should...just pick a random record..right down the values and test to see if it gets the results you want.
After that i would really like to see the form with some data...even fake ones

Here is some sample data.

Thanks,
Ben
Book1.xlsx

ok ...and what you want to do ...

OK.  So the lookup query aggregates data.  Joining to it will make your form not updateable.  Have you tested inside an event so you can put the criteria into a string and print it to see the problem?  It should be quite obvious.

ok ...and what you want to do ...

I need to assign the following control source to a text box vy code.

=dlookup("CountOfID","Progress_Note_GroupBy_VisitDate_Qry","VisitDate = #" & [visit_date] & "# and [Nurse_User_ID_num_pn] = '" & [Nurse_User_ID_num_snv] & "' And ShiftFromHour = " & [ShiftFromHour] )

Select allOpen in new window

Thanks,
Ben

And do remember the Dlookup will only give you one value....say that you have 10 nurses...you won't get the CountID of all the nurses individually...you will just get a number that will be the same for all the records...e.g 14..
i reckon that you want to display something like the No of shifts for each nurse...this won't do it

@Pat, John,

i reckon that you want to display something like the No of shifts for each nurse...this won't do it

I think you're big mistaken on this point.
I just tried the following in a text box and get a different value for each record.

=DLookUp("Client_First_Name","Skilled_Nursing_Visit_Note","ID = " & [id])

Select allOpen in new window

No doubt this is possible on a continuous form, just try it at your own app..
Can post a screenshot if necessary.

Thanks,
Ben

I said that it might work and it turns out that it does now that you've corrected the syntax error.  Good for you.  I see that the criteria is now ONE field rather than four.  Perhaps you were over complicating it earlier.

You will note that my original comment was:

The DLookup() had a lot of errors.  That is why it isn't working.

And I tried to fix the syntax.

now that you've corrected the syntax error.  Good for you.

Oh no, I just meant to prove the concept should work.
We just need it to make it work on the case I OP.
Sorry for the confusion caused..

Thanks,
Ben

Did you at least TRY to follow my directions on how to get Access to help you with the syntax errors?

@Jim,
See reason above.

however would like that to be done only upon user clicking on a button as not to cause unnecessary slowness.

Thanks,
Ben

@Pat,
I have tried last night a couple of times and was not able to get it working.
see for example.

Private Sub Command103_Click()
Dim s As String
's"=dlookup(""CountOfID"","Progress_Note_GroupBy_VisitDate_Qry","VisitDate = #" & [visit_date] & "# and [Nurse_User_ID_num_pn] = '" & [Nurse_User_ID_num_snv] & "' And ShiftFromHour = '" & [ShiftFromHour] & "'")"
    Me.TextCountOfPN.ControlSource = Me.TextCountOfPN.Tag
   ' Me.Repaint
End Sub

Select allOpen in new window

Thanks,
Ben

1. there is no need to keep the statement in the tag property.  It can just stay in the ControlSource of the control
2. You could put the DLookup() in the query.  Running domain functions inside loops is bad but it is not "more bad" to do it in the query rather than a ControlSource so whichever is easier to implement is what you should do.

@pat,
I'm trying to do it via code and not the regular way as this will only occur upon user requesting it.

FYI- I have the same concept, stored in a controls tag and works perfectly fine as follows.

=IsSchedDup([Client_First_Name],[Client_Last_Name],[Visit_Date],[Shift_From_Hour],[Nurse_Signature_First_Name],[Nurse_Signature_Last_Name])

Select allOpen in new window

Thanks,
Ben

Now i got the "idea"...i thought you wanted to pass the result of Dlookup to ControlSource
So if you need this trickery then you should write it like that

eval("dlookup(""ContactPerson"",""ClientContact"",""ClientID =0"") ")

Select allOpen in new window

Just replace the fields with the appropriate ones and you should be good to go.

@John,
Tried the following (in tag property) and getting an #Name?

eval("dlookup("CountOfID","Progress_Note_GroupBy_VisitDate_Qry","VisitDate = #" & [visit_date] & "# and [Nurse_User_ID_num_pn] = '" & [Nurse_User_ID_num_snv] & "' And ShiftFromHour = " & [ShiftFromHour] )")

Select allOpen in new window

also tried =eval and got syntax error.

Thanks,
Ben

I'm trying to do it via code and not the regular way as this will only occur upon user requesting it.

OK.  But it's been over 2H since I suggested a way to debug the where clause.  Have you tried my suggestion yet?

@Pat, trying now..

    Me.TextCountOfPN.ControlSource = Eval("dlookup(""CountOfID"",""Progress_Note_GroupBy_VisitDate_Qry"","" VisitDate = #"" & [visit_date] & ""# and [Nurse_User_ID_num_pn] = '"" & [Nurse_User_ID_num_snv] & ""' And ShiftFromHour = "" & [ShiftFromHour] )")

Select allOpen in new window

Message pops up, cant find field/s so and so, but they definitely exists, something else must be the problem..

Thanks,
Ben

take extra extra caution about the number of double quotes and it will work
take this expression to the immediate window and check if it works

Eval("dlookup(""CountOfID"",""Progress_Note_GroupBy_VisitDate_Qry"","" VisitDate = #"" & [visit_date] & ""# and [Nurse_User_ID_num_pn] = '"" & [Nurse_User_ID_num_snv] & ""' And ShiftFromHour = "" & [ShiftFromHour] )")

Select allOpen in new window

Just substitute the values to some real ones and check the result

If your syntax is not correct (and it is worse now), using Eval() isn't going to solve your problem.

I'm done.

@John,
what is mine any different than yours?
Thanks,
Ben

I tested it and it was working...just make a test to see if something is misplaced.

@Dale,

Will test that tom at work and keep you posted.

Thanks,
Ben

Hi Experts,
At the moment I took Jim's approach despite the inefficiency.. as the other suggestions were taking too much time of my work and I got shifted to other tasks, perhaps will return to this in another thread..
Thanks to all for your help!!