Full Version: Sql As Record Source For A Form
UtterAccess Forums > Microsoft® Access > Access Forms
mgwat69
I am a volunteer at an elderly care home. I am trying to help one of our supply employees with a form he uses called DME Check Out which writes to the DME Check Out table. The form currently has three fields: Equipment ID number (FAS number in the DME Check Out table and the DME Master table) Patient ID Number (ID# in the DME Check Out table and the Client Data table), and Start Date (Start Date in the DME Check Out table). In order for the user to enter the Patient ID Number they have to look at another data base ( VistaKEANE) which contains all of the Patient information. Once the information is entered into the form, the user clicks the >* in the lower left corner of the form and the information is written to the DME Check Out table. The supply person then enters the Patient First Name and Last Name manually into both the DME Check Out table and the Client Data table. He also has several other fields and check boxes to fill in. In addition he has queries that look up the patient by last name or by ID#. We want to replace the Patient ID data entry field with a list box that gets its information from the VistaKEANE database directly and writes it to the DME Check Out table. The table that VistaKEANE uses is called the br549_Resident table. I have successfully replaced the Patient ID Number data entry field with the list box that shows the patient’s last name, first name and ID# but the form does not provide the >* for the write process. Please note that we are not professional developers, so there are spaces in the field names and table names.
The Record Source for the form currently in use is:
SELECT [DME Check Out].[ID#] AS [DME Check Out_ID#], [DME Check Out].[FAS Number], [DME Check Out].[Start Date], [Client Data].[First Name], [Client Data].[Last Name], [Client Data].[ID#] AS [Client Data_ID#], [Client Data].[Room No], [DME Master].[DME Type] FROM ([DME Master] INNER JOIN ([Client Data] INNER JOIN [DME Check Out] ON [Client Data].[ID#] =[DME Check Out].[ID#]) ON [DME Master].[FAS Number] =[DME Check Out].[FAS Number])
Note that the ID# is a number in the DME Check Out table and the cooresponding field in the br549_Resident table is health_rec_nbr and is text so I added a field to the DME Check Out table called Text_ID and used a query to update the table.
Otried the following record source on the new form with the list box fields declared in a VBA module. Note that column (0) is res_snbr, the key in the br549_Resident table which came into the list box automatically when I used the wizard to create the list box.
SELECT [DME Check Out].[FAS Number], """&Me.List18.column(1)&""", """&Me.List18.column(2)&""", """&Me.List18.column(3)&""", [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];
This wrote to the table but understandable did not write the correct last name, first name and Text_ID, so I tried the following as a test to see if I could write the correct last name.
SELECT [DME Check Out].[FAS Number], [DME Check Out].[Last Name] AS ""&Me.List18.column(1)&"", [DME Check Out].[First Name] AS """&Me.List18.column(2)&""", [DME Check Out].[Text_ID] AS """&Me.List18.column(3)&""", [DME Check Out].[Start Date], [DME Master].[DME Type];
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];
I got the error message “The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Please help.
Daryl S
Remove the semi-colon before the FROM clause.
Daryl
mgwat69
ok, thanks, that extra semi-colon was stupid on my part. Now I tried
SELECT [DME Check Out].[FAS Number] AS [FAS Number], [DME Check Out].[Last Name] AS ["""&Me.list18.column(1)&"""], [DME Check Out].[First Name] AS ["""&Me.column(2)&"""], [DME Check Out].Text_ID AS ["""&Me.column(3)&"""], [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number]
And got the error """&Me.list18.column(1)&""" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long
Oalso tried removing first one set of quotes, then two sets of quaotes then all three sets of quotes, then the ampersand, then the ME. Still not luck. The error message remained the same.
I finally got down to
SELECT [DME Check Out].[FAS Number] AS [FAS Number], [DME Check Out].[Last Name] AS [column(1)], [DME Check Out].[First Name] AS [column(2)], [DME Check Out].Text_ID AS [column(3)], [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];
No error message, but it did not write to the DME Check Out table correctly.
ALaRiva
What is column(2) and column(3)? A field in a table? A control on a form?
Daryl S
Is there a reason you are using "[column(1)]" and "[column(2)]" instead of just using the table's field names? If those are the field names, then I would suggest renaming them in the table and in the query. You don't usually use aliases in a query that is used to populate a form. You can just change the label names on the form if you want better descriptions.
Is for your query, does the data look right when you display it? Is the query producing incorrect data, or is the data from the query not getting into the table properly? If it is the latter, then check your field names.
Oassume you are not trying to update anything in the DME Master Data table, as that is the 'one' side of a 'one to many' relationship. Also, I would add [Client Data].[ID#] in the SELECT clause as Access needs the primary key available for updates.
If you don't get it figured out, then post the structure of your DME Check Out table, and tell us what is and is not working.
- Daryl
mgwat69
Anthony,
Oconstructed the list box using the wizard by pulling in fields from the br549_Resident table: res_last_name, res_first_name, and health_rec_nbr. The primary key, res_snbr came in automatically as column (0) with a width of 0 and the other three fields are column(1), column(2), and column(3) respectively. I have them declared in a VBA module as follows:
ption Compare Database
list18.Column (0)
list18.Column (1)
list18.Column (2)
list18.Column (3)
Daryl,
I tried
SELECT [DME Check Out].[FAS Number], br549_Resident.res_first_name, br549_Resident.res_last_name, br549_Resident.health_rec_nbr, [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN (br549_Resident INNER JOIN [DME Check Out] ON br549_Resident.health_rec_nbr = [DME Check Out].Text_ID) ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];
I did not get any SQL error messages, but the form would not respond the way we want it to. The >* in the bottom left hand corner of the form remained a light gray and would not respond to a click in order to write the record to the table.
The final part of the above SQL checks to make sure that the FAS number entered by the user is indeed in the DME Master table and an error message is displayed to the user if FAS is not in the DME Master table.
Note that ID# in the DME Check Out table is numeric and the health_rec_nbr in the br549_Resident table is text so I had to create another field in the DME Check Out table and called it Text_ID so I wouldn’t get a type mismatch. I then used a query to copy the Text_ID from the ID# and format it so it would compare with health_rec_nbr.
When I use
SELECT [DME Check Out].[FAS Number], """&Me.List18.column(1)&""", """&Me.List18.column(2)&""", """&Me.List18.column(3)&""", [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];
the FAS number and Start Date writes correctly but the [Last Name], [First Name] and Text_ID write a haphazard name from the table. The ID# also writes but does not match the Text_ID. I want to stop the ID# from writing at all unless I can get it to write correctly.
Daryl S
For best database design, since you can link to the VistaKEANE database directly, you should only be storing the Patient ID Number in your Access tables. The name can be displayed on any form or query from the linked table. That said, if you want to store the name in the Access tables as well as the ID, then you would normally approach it like this:
The recordsource for your form should be what the old Record Source was (using the First Name and Last Name from the client data table). When a new record is added, and the ID# is chosen from the list box, then you have code in the Click event (or AfterUpdate event) of the list box to put the First Name and Last Name in their proper fields, like this:
Me.[First Name] = Me.List18.column(1) 'second column in list box
Me.[Last Name] = Me.List18.column(2) 'third column in list box
The record source of a form not only pulls data from the table into fields, but it contains what will be put back into the table (on an update) or entered into a table (for a new record) when the record is saved. So the record source must contain the field where you want the data stored.
Does that help?
- Daryl
mgwat69
I’m not sure if that helps. The purpose of this change to the form is to eliminate the need for the Client Data table. It has been maintained physically by the equipment person by looking up information in VistaKeane. The VistaKeane database is the master of the entire facility. It is where the client information is entered when the client checks into the facility.
Orealize we can do queries which can cross reference the databases, but we are amateurs at Access and thought writing to the table would be easier. Besides, there are check boxes in the DME Check Out table that indicate billing method that must be filled in. These are not found in VistaKeane and I’m not sure how to make those in a query.
Also remember that the ID# in the DME Checkout table is numeric and the corresponding health_rec_nbr in VistaKeane is text. I added another field to the DME Check Out table and did a query to update and format the new field called Text_ID so I can make a comparison without a type mismatch.
Since the form was writing the FAS number and the Start Date correctly, I tried to adapt your suggestion by using the following:
SELECT [DME Check Out].[FAS Number] AS [FAS Number], [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];
And then this in the AfterUpdate:
Option Compare Database
Private Sub Form_AfterUpdate()
Me.[Last Name] = Me.List18.Column(1)
Me.[First Name] = Me.List18.Column(2)
Me.[Text_ID] = Me.List18.Column(3)
End Sub
I got a “compile error: Invalid outside parameter” and it shaded the 0 in List18.Column(0) in my VBA module. I tried taking out that line and I got the same error and it shaded the 1 in List18.Column(1). So I took out the VBA module completely. The form wrote the FAS Number and the Start Date correctly, but still wrote a haphazard Last Name, First Name, ID#, and Text_ID.
Daryl S
I think your problem is that you are using the form's AfterUpdate event instead of the combo box's AfterUpdate event. Move your code and see if that helps.
Daryl
mgwat69
That didn't do it. Still haphazardly writng Last Name, First Name, ID#, and Text_ID
Daryl S
Add some debug.print statements in your code (or add a breakpoint and check the values):
Private Sub Form_AfterUpdate()
Debug.Print Me.List18.Column(1) & "; " & Me.List18.Column(2) & "; " & Me.List18.Column(3)
Me.[Last Name] = Me.List18.Column(1)
Me.[First Name] = Me.List18.Column(2)
Me.[Text_ID] = Me.List18.Column(3)
End Sub
Let us know what is in the immediate window of the code pane when you run this. Have you changed the name of your listbox or anything? Do you have any procedures, modules, or variables named List18 by chance?
- Daryl
mgwat69
I can't get that far. I am still getting a "Compile error: Invalid outside procedure" when I click on the list box. My module is called Module1. The error then shows Module1 and shades the 0 in List18.Column (0). If I try removing that line of code I get the same compile error and it shades the 1 in List18.Column (1)
Daryl S
Go into the properties for List18, and tell us what you see for these properties:
ame
Row Source Type
row Source
Bound Column
Column Count
Column Widths
mgwat69
name: List18
Row Source type: Table/Query
row source: SELECT [br549_Resident].[res_snbr], [br549_Resident].[res_last_name], [br549_Resident].[res_first_name], [br549_Resident].[health_rec_nbr] FROM [br549_Resident] ORDER BY [res_last_name];
bound column: 1
Column Count: 4
Column Widths: 0";1.5625";1.7605";1"
constructed this list box using the wizard.
Daryl S
Reading back to Post 6 in your response to Anthony - you say you have declared list18.column(0), etc. Remove that code (or at least comment it out) - you do not want to declare those as variables, since they are references to the list18 object.
You don't want to have two things with the same reference.
- Daryl
mgwat69
OK I deleted the module that declared the lis18 columns.
Onow have the after update event procedure:
Option Compare Database
Private Sub List18_AfterUpdate()
Debug.Print " & Me.List18.Column(1) & "; " & Me.List18.Column(2) & "; " & Me.List18.Column(3) & "
Me.[Last Name] = Me.List18.Column(1)
Me.[First Name] = Me.List18.Column(2)
Me.[Text_ID] = Me.List18.Column(3)
End Sub
Now when I click on the list box I get:
run-time error '2465'
Microsoft Office Access cannot find the field '|'referred to in your expression.
The debugger highlights the line:
Me.[Last Name] = Me.List18.Column(1)
Nothing happens with the Debug.Print
I Googled the run-time error to see what I can find:
"The error 2465 sometimes means the [] are not valid required or what have you"
I read some of the other Google results but I'm not sure they apply here.
I tried to change the Last Name to Last_Name but the table is read only and would not allow the change.
Thanks so much for spending so much time on this. I really appreciate it and I hope you don't give up on me.
Daryl S
Change this:
Debug.Print " & Me.List18.Column(1) & "; " & Me.List18.Column(2) & "; " & Me.List18.Column(3) & "
to this:
Debug.Print Me.List18.Column(1) & "; " & Me.List18.Column(2) & "; " & Me.List18.Column(3)
Daryl
mgwat69
Nothing happens with the Debug.Print
When I click on the list box I still get:
run-time error '2465'
Microsoft Office Access cannot find the field '|'referred to in your expression.
The debugger highlights the line:
Me.[Last Name] = Me.List18.Column(1)
Daryl S
Could your control name for [Last Name] be something else? In design mode, select the control (text box?) that should hold the [Last Name] field, and go to the properties window and see what the name is. Maybe the control name is different, like txtLastName. If so, change that in the statement:
Me.txtLastName = Me.List18.Column(1)
You might want to try typing that same line in again, and watching what Access provides as you type, to make sure it uses correct names.
Let us know!
- Daryl
mgwat69
Sorry I have been away from this project for awhile.
Otried retyping the line in the after update code and nothing relevant came up.
The row source of the textbox is SELECT [br549_Resident].[res_snbr], [br549_Resident].[res_last_name], [br549_Resident].[res_first_name], [br549_Resident].[health_rec_nbr] FROM [br549_Resident] ORDER BY [res_last_name];
The fields in the DME Check Out table I am trying to write to are indeed [Last Name], [First Name] and Text_ID, so I am not quite sure what you meant in your last post.
Thanks for your patience.
Daryl S
If you have a code pane open, the debug.print should print the chosen record from the combo box to the immediate window of the code pane. Let me know if you are not getting anything in the immediate window.
How on your form, find the name of the text box that holds the Last Name. What is the Name property of the textbox?
Finally, what happens if you start to re-type Me.Last on the line below the Me.[Last Name] = Me.List18.Columkn(1)? Does Access automatically recognize Last Name as a control, and does it put the square brackets around the Last Name by itself?
Let us know...
- Daryl
mgwat69
Nothing comes up in the code pane.
There is no text box for last name. The only text boxes are FAS number (equipment number), then the list box, then the start date text box.
When I retype, Last Name does not come up in the suggestions.
Daryl S
OK, it sounds like [Last Name] might be the problem. Check the record source for the form. I thought we were working with something like this:
SELECT [DME Check Out].[FAS Number] AS [FAS Number], [DME Check Out].[Last Name] , [DME Check Out].[First Name], [DME Check Out].Text_ID , [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];
If it has changed, then let me know. If not, you should have textboxes on the form for the [First Name] and [Last Name]. If the fields are not on the form, then we can't update them from the listbox.
So check the record source and the form. Make sure the [Last Name] and [First Name] fields are on the form, and try again...
- Daryl
mgwat69
Sorry I haven't replied sooner, I had other projects that required my attention.
Thanks so much for all your help, I am not sure whether we will continue pursuing this or not.
Daryl S
You can always come back and post with new questions...
Good luck on your project!
- Daryl
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.