Macros in forms based on different tables - a syntax question

[Walt in Decatur]

I'm an amateur at databases, so forgive this rookie question.

I am developing a database to capture building planning information

I have a form that deposits general data about a given space into a table. On that form I have a command button which invokes a macro that filters the records based on a particular criteria (e.g. only spaces that are laboratories). This works just fine.

I also have another form that deals with the plumbing services for the space. This form deposits data into a different table. The two tables are joined via a field called "Room Name". I have copied the above-mentioned command button to this form as well.

The problem is that I get the "Enter Parameter Value" prompt when I attempt to use the command button. So there may be some syntax or procedure boo-boo I've made in making the reference to the tables.

I've also tried it by basing the second form on a query which takes data from both tables. The command buttons and macros work OK, in this case. But I'm unable to input data. So either way, I'm in deep doo-doo.

Any help would be appreciated.

Here is the applyfilter statement from the macro:

[tbl_general_data].[Super-Category]="Laboratory Space" And [tbl_general_data].[Number of Spaces - Design]<>0

the second form is based on tbl_piped_lab_services_data

[MMcCarthy]

Sorry but this question is very confusing.

Firstly you said you copied a command button from one form to another. Don't do this as it probably won't work.

Try creating your command button using the wizard on the second form and see what happens.

[Walt in Decatur]

Gee, I thought I was quite clear... :-(
I tried the "use the wizard to create the command button" method - it didn't work.

Again, the basic idea is that the command button calls an event procedure - in this case a macro that applies a filter to the records so only a certain types show up in the form. Macro's "WHERE" statement refers to a table where I have dumped general data. It is there that the field resides which the "WHERE" statement references. As I've said, this all works perfectly on a form which is directly based on this same table. But it won't work when the form is based on another table. Of course, the reason for a slew of tables is that I wanted to "normalize" the relational database.

Could a dLookUp function provide a work-around? Is this a syntax error or a coding issue? Would it work it if these were a form and a subform? I don't know, as I've said, I have no formal training in any of this.

Any and all hints or suggestions will be appreciated. Extensive web search has not yet turned up an idea of what might not be working.

Sorry but this question is very confusing.

Firstly you said you copied a command button from one form to another. Don't do this as it probably won't work.

Try creating your command button using the wizard on the second form and see what happens.

[MMcCarthy]

I understand what a filter is what I don't know is what exactly your filter is working on. Can you give the exact SQL statement that the form being filtered is based on. Without more information the best guess I can make is that you are filtering a query that is already filtered.

e.g. If your form was based on a query like

 
SELECT EmpID, EmpName, DeptID, JoinDate 
FROM Employees
WHERE JoinDate > #01/01/07#
 

Then if you tried to add a filter

WHERE DeptID = 23

You would now have an invalid sql statement with two where statements. The filter should be:

AND DeptID = 23

For the moment this is guesswork as I don't know what your current sql query is.

Mary

[Walt in Decatur]

Mary:

Thanks for helping, that's very kind of you. Please bear with me just a little bit longer.

I've actually tried to solve this problem differently. I've based the send form on a query that combines fields from two different tables (see SQL for this query below). Now my filters work, but I'm unable to enter any data - which is the purpose of the form. I don't know what I need to change about the way the form or the underlying query is structured to allow data input.

Here is the query SQL:

 
SELECT tbl_general_data.[Room Name], tbl_general_data.Department, tbl_general_data.[Functional Unit], tbl_general_data.[Space Type], tbl_general_data.[Design Area], tbl_general_data.[Number of People], tbl_general_data.[Number of Spaces - Design], tbl_general_data.[Super-Category], tbl_general_data.[Space Category], tbl_AV_data.[Projection Screens], tbl_AV_data.[Projection Screens Number], tbl_AV_data.[Projection Screens Type], tbl_AV_data.Projectors, tbl_AV_data.[Projectors Number], tbl_AV_data.[Projectors Mounting], tbl_AV_data.[Video Cameras], tbl_AV_data.[Video Cameras Number], tbl_AV_data.[Camera 1 Mounting], tbl_AV_data.[Camera 2 Mounting], tbl_AV_data.[Camera 3 Mounting], tbl_AV_data.[Camera 4 Mounting], tbl_AV_data.[TV Monitors], tbl_AV_data.[TV Monitors Number], tbl_AV_data.[Writing Surfaces], tbl_AV_data.[Writing Surfaces Type], tbl_AV_data.[Interactive Whiteboards], tbl_AV_data.Microphones, tbl_AV_data.[Microphones Number], tbl_AV_data.Speakers, tbl_AV_data.[Speakers Number], tbl_AV_data.[Speakers Mounting], tbl_AV_data.[Audience Participation System]
FROM tbl_general_data INNER JOIN tbl_AV_data ON tbl_general_data.[Room Name] = tbl_AV_data.[Room Name];
 

Walter

[MMcCarthy]

Hi Walter,

At a Guess I'd say your query's not updateable. Try this instead ...

 
SELECT tbl_general_data.*, tbl_AV_data.* 
FROM tbl_general_data INNER JOIN tbl_AV_data 
ON tbl_general_data.[Room Name] = tbl_AV_data.[Room Name];
 

This assumes there is only a one to one relationship between theses two tables. If there is a one to many relationship you can't have this as the query of the form. You will need to look at subforms.

[Walt in Decatur]

I have solved the problem by structuring the forms the way I think they ought to have been in the first place, that is to have all of the secondary forms relating to inputing data for a given space become subforms of the main form. I just took the forms I had already made, did some graphical reformatting, and made each of them as the source of the subform object on the main form. The command buttons that filter the records now only need to exist on the main form and work just as they did before. Because the main form and subforms are linked via the [Room Name] field in a one-to-one relationship, the main form and subforms are properly synchronized, and since each subform is only tied to a single table (all of the tables in question are in a proper relational relationship already), I can update the data.

All the answers I received were helpful to get me to think the problem through better. Your patience with me is much appreciated.

Walter

Hi Walter,

At a Guess I'd say your query's not updateable. Try this instead ...

 
SELECT tbl_general_data.*, tbl_AV_data.* 
FROM tbl_general_data INNER JOIN tbl_AV_data 
ON tbl_general_data.[Room Name] = tbl_AV_data.[Room Name];
 

This assumes there is only a one to one relationship between theses two tables. If there is a one to many relationship you can't have this as the query of the form. You will need to look at subforms.

[MMcCarthy]

You're welcome Walter.

Glad you got it working.

Mary