haytatreides
IS-IT--Management
i am a bit new at this, so i am sorry for what i am sure is probably really easy to you all, but i need to set a few variables to be the result of a query. can anyone help?
hayt
hayt
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
set variable to query result 2
- Thread starter haytatreides
- Start date
- Status
-
2
- #2
Queries can return multiple fields and multiple records. That in mind, the simplest method is to use DLookup() ie:
Dim myVar as Variant 'or whatever
myVar = DLookup("[MyField]","qselMyQuery","my where clause"
Duane
MS Access MVP
Dim myVar as Variant 'or whatever
myVar = DLookup("[MyField]","qselMyQuery","my where clause"
Duane
MS Access MVP
- Thread starter
- #3
haytatreides
IS-IT--Management
I thought dllokup was "field, table, criteria." will that work? (sorry, i don't know what u mean by qselMyQuery - you mean select from sql)? and where under criteria?) (Am i making any sense?)
- Thread starter
- #4
haytatreides
IS-IT--Management
i got it! Thanks, you rock, that did it!
1. 'NCMR Form' is a form with the majority of the fields bound to table 'NCMR Table'.
2. 'NCMR Form' has an invisible field 'ApproverCode' (i.e. passcode) and a visible field named 'Signature', both of which are bound to table 'Signature Table'. The related fields in 'Signature Table' are 'ApprID' and 'ApprName'.
3. 'Signature Query' is bound to 'Signature Table' and prompts for the approver code in the 'ApprID' field to return the signature associated with that approver code in the 'ApprName' field .
I would like to have 'Signature Query' run in 'NCMR Form' on 'ApproverCode__AfterUpdate' and update the field 'Signature' in 'NCMR Form' with the associated approver's name (signature). If an invalid passcode is entered, the field should return a null value with an 'invalid password' message.
I've tried this both within the form and as a subform but while my DLookup statement does not deliver an error message, it doesn't give me the signature either!
I would like to display 'ApprName' on a form 'NCMR Form' when the form is bound to " 'Signature Table','ApprName'...... or at least that's what I think I want???
The objective is to return an electronic signature to the 'NCMR Form' 'Signature' field by having each valid approver enter a unique code that has been assigned to him/her.
The more I've tried to work through this, the more lost I am! At this point I can't really re-design the database since I have another large one in the design process right now that has a short deadline.
2. 'NCMR Form' has an invisible field 'ApproverCode' (i.e. passcode) and a visible field named 'Signature', both of which are bound to table 'Signature Table'. The related fields in 'Signature Table' are 'ApprID' and 'ApprName'.
3. 'Signature Query' is bound to 'Signature Table' and prompts for the approver code in the 'ApprID' field to return the signature associated with that approver code in the 'ApprName' field .
I would like to have 'Signature Query' run in 'NCMR Form' on 'ApproverCode__AfterUpdate' and update the field 'Signature' in 'NCMR Form' with the associated approver's name (signature). If an invalid passcode is entered, the field should return a null value with an 'invalid password' message.
I've tried this both within the form and as a subform but while my DLookup statement does not deliver an error message, it doesn't give me the signature either!
I would like to display 'ApprName' on a form 'NCMR Form' when the form is bound to " 'Signature Table','ApprName'...... or at least that's what I think I want???
The objective is to return an electronic signature to the 'NCMR Form' 'Signature' field by having each valid approver enter a unique code that has been assigned to him/her.
The more I've tried to work through this, the more lost I am! At this point I can't really re-design the database since I have another large one in the design process right now that has a short deadline.
I find issues since NCMR form is bound to NCMR table, how can two other controls be bound to Signature table? I'm sorry but if I can't understand the first two points, I have trouble reading any of the remainder.
A single form can only be bound to one record source. This can be a query or sql based on more than one table. You can use combo boxes, list boxes, subforms, domain functions, and code to display related values from other tables/queries.
Duane
MS Access MVP
A single form can only be bound to one record source. This can be a query or sql based on more than one table. You can use combo boxes, list boxes, subforms, domain functions, and code to display related values from other tables/queries.
Duane
MS Access MVP
Okay, let me try the second path. I have a form 'NCMR' with all fields bound to table 'NCMRTable'. I added an unbound text box 'ApprSig' and named its Control Source as:
=DLookUp("[ApprSig]","ApprTable","[ApprCode] =" & [Forms]![NCMR]!ApprNumber).
This DLookup statement successfully updates the 'ProdApprSig' field with the appropriate text from the table 'ApprTable', which contains 2 fields, 'ApprCode' and 'ApprSig'.
After the 'ApprSig' is updated on the form 'NCMR', I need to either hide the 'ApprCode' field on the form or otherwise obscure the value entered, since the 'ApprCode' is a unique passcode.
My first thought was to run a query at this point on the 'ApprTable' fields; however, I couldn't get it to update the 'NCMR' form 'ApprSig' field.
All I really want is for the approver to enter his passcode so an electronic signature can be completed on the form 'NCMR' -- without revealing the passcode to anyone else updating the same form.
=DLookUp("[ApprSig]","ApprTable","[ApprCode] =" & [Forms]![NCMR]!ApprNumber).
This DLookup statement successfully updates the 'ProdApprSig' field with the appropriate text from the table 'ApprTable', which contains 2 fields, 'ApprCode' and 'ApprSig'.
After the 'ApprSig' is updated on the form 'NCMR', I need to either hide the 'ApprCode' field on the form or otherwise obscure the value entered, since the 'ApprCode' is a unique passcode.
My first thought was to run a query at this point on the 'ApprTable' fields; however, I couldn't get it to update the 'NCMR' form 'ApprSig' field.
All I really want is for the approver to enter his passcode so an electronic signature can be completed on the form 'NCMR' -- without revealing the passcode to anyone else updating the same form.
I need either the value from 'ApprCode' or 'ApprSig' from form 'NCMR' to be recorded in table 'NCMRTable'. Since I made the 'ApprSig' Control Source a DLookup, am I correct that I can't record that value in my table?
How do I set the input mask to Password?
I apologize for my ignorance but a lot of this is unfamiliar territory. I've used it on the data input end but never had to design databases with it. I took Paradox in college but that was a long, long time ago and it really doesn't translate; I never used it much anyway. I'm going through an Access self-study but sometimes the need to meet a work deadline comes faster than my understanding does.
How do I set the input mask to Password?
I apologize for my ignorance but a lot of this is unfamiliar territory. I've used it on the data input end but never had to design databases with it. I took Paradox in college but that was a long, long time ago and it really doesn't translate; I never used it much anyway. I'm going through an Access self-study but sometimes the need to meet a work deadline comes faster than my understanding does.
I know that you don't want to show the ApprNumber/ApprCode but do you care if ApprSig is visible? At what point do you want to add the ApprSig into the record? Is this for a new record or is there some other event that occurs causing the entry of the ApprSig into the table?
To set a input mask of Password... call up Help and enter either Input Mask or Password.
Duane
MS Access MVP
To set a input mask of Password... call up Help and enter either Input Mask or Password.
Duane
MS Access MVP
'ApprSig' needs to be visible.
The Password input mask did exactly what I needed to do! Thanks so very much for you help with that (how did I miss that one when I've been there a hundred times setting up date input masks????)
The original form is called up from a Switchboard in Add mode (in response to a manufacturing problem) to initiate a corrective action. Initial data entry (manufacturing problem detail) is performed and saved to table 'NCMR'. After problem resolution in the manufacturing area has been performed and the related problem resolved, a form populated with the initial data entry info is opened from the Switchboard in Edit mode. This occurs when the user enters the related NCMR number to open a twin NCMR number query-based form. At this point, a valid approver for the related problem type needs to approve the manufacturing problem resolution that took place. This is where the passcode 'ApprCode' needs to be entered and the approver's signature affixed.
With the Password mask taking care of my 'ApprCode' issue, the final step is to have the 'ApprSig' recorded in table 'NCMR'.
The Password input mask did exactly what I needed to do! Thanks so very much for you help with that (how did I miss that one when I've been there a hundred times setting up date input masks????)
The original form is called up from a Switchboard in Add mode (in response to a manufacturing problem) to initiate a corrective action. Initial data entry (manufacturing problem detail) is performed and saved to table 'NCMR'. After problem resolution in the manufacturing area has been performed and the related problem resolved, a form populated with the initial data entry info is opened from the Switchboard in Edit mode. This occurs when the user enters the related NCMR number to open a twin NCMR number query-based form. At this point, a valid approver for the related problem type needs to approve the manufacturing problem resolution that took place. This is where the passcode 'ApprCode' needs to be entered and the approver's signature affixed.
With the Password mask taking care of my 'ApprCode' issue, the final step is to have the 'ApprSig' recorded in table 'NCMR'.
Assuming you have a text box for ApprSig named "txtApprSig" and a text box with a Password Iput Box named "txtApprCode". The txtApprSig box should be bound to the ApprSig field and should be locked and disabled.
In the after update event code for txtApprCode, add this code:
Me.txtApprSig = DLookUp("[ApprSig]", _
"ApprTable", "[ApprCode] =" & " & _
Me.txtApprCode)
This assumes ApprCode in numeric.
Duane
MS Access MVP
In the after update event code for txtApprCode, add this code:
Me.txtApprSig = DLookUp("[ApprSig]", _
"ApprTable", "[ApprCode] =" & " & _
Me.txtApprCode)
This assumes ApprCode in numeric.
Duane
MS Access MVP
I can't thank you enough - this worked out perfectly! I now have a masked password code with an auto-update for the signature and approval date. I learned a ton from experimenting around with this after you gave me the info I needed to get headed in the right direction.
![[bigsmile]](/data/assets/smilies/bigsmile.gif "[bigsmile] [bigsmile]")
.- Status
Similar threads
- Question
- Locked
- Question