Pass Value to Form

[JohnBoy]

Wonder if someone could point me in the right direction here,


I have a form that is used for auditing, it displays various data in relation to a patient however due to space etc I have inserted a column of fields that will display what’s called dagger and asterisks values, as these are not common I just want the user to be able to look up the value’s meaning from a table and the meaning to be displayed in a Form..
So I have placed buttons down the right side of these fields for them to click on,
So easy enough in my mind, just build a query that takes it’s value from the field in the form,
That works for the 1st one and then I thought hang on “Do I really want to build 14 queries etc”

So to VBA, so next thought is lets put a bit of SQL behind each button and change the last bit of the code to reference the field it relates to... solved... err no.. not quite as I am not sure how to get it to display the result in the form and don’t think I need to build 14 separate form..???
The SQL Behind the original query is:
SELECT ICD10_LOCAL.CODE, ICD10_LOCAL.DESCRIPTION
FROM ICD10_LOCAL
WHERE (((ICD10_LOCAL.CODE)=[Forms]![frm_Audit].[Diag_supl_1]));

Diag_supl_1 goes from 1 to 14 hence the 14 buttons for looking up,
(I have put a screen shot on as an attachment)
So at present I have the first D-A Code button open a form called frm_Dagger_Asterisk which takes a feed from a query with the above SQL running it, Am I going the right way using SQL behind each button and if so how do I get the result from the SQL to open the form and display the value for that particular field... any help appreciated

[pbaldy]

Well, you could pass the entire SQL or the control or whatever in OpenArgs, and use that in the form's open event to set the form's record source. I'd probably pass the value and then use that in the SQL.

[JohnBoy]

Cheers for the swift reply, so are you saying place the sql in the forms on open event with an * or something like that instead of the 1,2,3---to 14 etc, I gave that a try just using the one field but get the #Name error in the form, I also tried in the record source field of the forms properties but that throws up an error to, ???

[JohnBoy]

I think I have sort of worked things out here to some extent,
In the back of the command button for the 2nd button I have entered the following code in the on click event, DoCmd.OpenForm frm_Dagger_Asterisk, acNormal, , , acFormReadOnly, acWindowNormal, Diag_code_2, am I right so far as I want the Diag_Code_2 value passed to the form, Next in the on open event for the form I have entered so far If Len(Me.OpenArgs) > 0 Then... its the next bit im stuck with, what I want it to do is take the openargs value passed to it and run a lookup/query/sql etc against a ref table and display it in the form... Heeeelppp

JB

[pbaldy]

You'd want to enclose Diag_code_2 in quotes, otherwise Access will think it's a variable or something. Then in the open event, after your test try this:

Me.RecordSource = "SELECT ICD10_LOCAL.CODE, ICD10_LOCAL.DESCRIPTION " _
& "FROM ICD10_LOCAL " _
& "WHERE ICD10_LOCAL.CODE=[Forms]![frm_Audit].[" & Me.OpenArgs & "]"

[JohnBoy]

Sorry for the late update but thanks for the advice it did the trick and I have now got it all sorted. JB