By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Autofilling field from query to form

P: 19
Hello - I've made a form with a subform of the same table, which is for Dr's notes. On the subform I have the query sorting in decending order and a criteria to enter a patient's name so the last note written for that patient shows on the subform.

How can I make it so that when a patient's name is entered for the subquery, that name automatically fills the name field on the blank note form? Or when a name is entered into the name field on the Blank note how can I have it run the subnote query to find the notes for that patient?

Any help would be greatly appreciated.

Thank you
Laura
Feb 24 '09 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,619
@Ledmark
Hello Laura, post the SQL, with the Criteria, that comprises the Record Source for your Sub-Form.
Feb 26 '09 #2

P: 19
OK - here is the SQL, including the criteria, for the sub form:

SELECT tblNote.NoteDate, tblNote.NotePtName, tblNote.[Note-S], tblNote.[Note-O], tblNote.[Note-A], tblNote.NoteReccomd
FROM tblNote
WHERE (((tblNote.NotePtName)=[Enter Patient's Name]))
ORDER BY tblNote.NoteDate DESC;

This is something else I tried - I put a combo box on the form called frmOptionBoard that has a list of patient names based on a table called tblIdentity. When a name is selected on the option board I want to then be able to click on one of the options, which opens a form and I'll use the frmNotes as an example, and when the form loads I want the form opening up to look at the patient name chosen in the Combo Box and then autofill it into the NotePtName field on the Note Form. I don't know how to say this in code - I tried this but it didn't work:

If Len(Me.[NotePtName] & "") = 0 Then
Me.[NotePtName] = Me.[frmOptionBoard.Combo26]
End If

I know what I want to do is possible but I just don't know how to word it.

Thanks again for any help.

Laura
Feb 27 '09 #3

ADezii
Expert 5K+
P: 8,619
@Ledmark
You can include the actual Parameter itself as a Field Name directly in the SQL, where it can easily be referenced as [PName]:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblNote.NoteDate, tblNote.NotePtName, tblNote.[Note-S], tblNote.[Note-O], tblNote.[Note-A], tblNote.NoteReccomd, 
  2. [Enter Patient's Name] As PName
  3. FROM tblNote
  4. WHERE (((tblNote.NotePtName)=[Enter Patient's Name]))
  5. ORDER BY tblNote.NoteDate DESC;
Feb 27 '09 #4

P: 19
OK - I'm assuming this is the SQL I put into the Sub Query so I'm going to give it a whirl.

Thank you for your help. If it doesn't work I'll be back :)

Laura
Feb 27 '09 #5

P: 19
OK - I put this exact thing into the Note Sub query:

SELECT tblNote.NoteDate, tblNote.NotePtName, tblNote.[Note-S], tblNote.[Note-O], tblNote.[Note-A], tblNote.NoteReccomd
[Enter Patient's Name] As PName
FROM tblNote
WHERE ((tblNote.NotePtName)=[Enter Patient's Name]))
ORDER BY tblNote.NoteDate DESC;

and tried to save it and this is what I got:

Syntax Error (missing operator) in Query Expression 'tblNote.Recommd [Enter Patient's Note]'.

Nothing I tried worked - maybe I'm just retarded :( frustrated.

Is there a way to write code for the Note form On Load that tells the form to look at the combo box for the name and have it autofill the NotePtName field and the parameter query?

What about something in the form properties On Load like this:

Me.[NotePtName] = Me.[frmOptionBoard.Combo26] Is it really this hard??

Laura
Feb 27 '09 #6

ADezii
Expert 5K+
P: 8,619
@Ledmark
Forgot the Comma after tblNote.NoteReccomd
Expand|Select|Wrap|Line Numbers
  1. tblNote.NoteReccomd,
  2. [Enter Patient's Name] As PName
Feb 27 '09 #7

P: 19
OK - I'll try it again. Thank you for all your help.

Laura
Feb 27 '09 #8

P: 19
I tried it again just like you said in the sub form query and nothing happened - it didn't work.

Laura
Feb 27 '09 #9

ADezii
Expert 5K+
P: 8,619
@Ledmark
Create another Text Box on the Sub-Form (you can hide it if you like), and set its Control Source to PName. You can now refer to this Field by:
Expand|Select|Wrap|Line Numbers
  1. Forms![Main Form]![Sub-Form Control Name].Form![PName]
Feb 27 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.