Nested queries and Forms

[ejoffe]

Hey,

I am working on my first access project (please be patient and try to explain like to a child). My problems however are past the basic level so please bear with me ....

I am working on a datbase which deals with laboratory data from patients which have different date stamps - ie. each record has a 'valid time' (time of blood drowing) a 'transaction time' (time of entry into the DB) and 'delete time' (deleted recordes are just given a date and not really deleted).

The idea is that the user should be able to watch the status of the DB at different past dates (ie what did the doctor know about a patient blood tests a month ago when he made the decision to give him a medication which eventually harmed his kideneys). The user should also be able to change values of records, delete records (which means to add a date to the deleted date field) and to add new records.

I want to do it all using one form.

Things I have done so far:
1. Query by form which locates a specific record (Main-Form & singleRecQuery)
2. A subform in Main-Form which displays the results of singleRecQuery (Single-subform). The query and subform aer updated by a macro once I enter all the data into main-form.
3. A qury by form derived from Main-Form which locates a range of values (Range-subfrom & Range-Query)

My problem spans both forms and queries (so I am posting in both forums):
1. I want to create an update query that will draw its data from Single-subform (the form which displays the results of singleRecQuery). I have created the qury without a problem but the fields in the form are locked for entering data (maybe because they are bound). Is there any way to enter data in such a form which is based on a query and then use the new data in another query ? (I tried allowing data entery in the forms properties but it deos't work).

2. I have tried to put a criterion in the queries on the "Delete Date" field so that the quries will not display the deleted records (I have tried "is null" "is empty" and "is missing") but when I run the query with these parameters I get no records back.

3. In the form I have a command buton which runs a macro that runs the qury and then update dthe subform. Is there a way to get this button to run different macros after different events in the form or do I have to add a button for every specific task I want.

4. As I have mentioned the Main-fom is supposed to run 3 differnt queries and display the results on the same form. I know how to make a subform invisable but is there a way to place all three result subforms in the same spot ?

Sorry it is so long
Thanks
EJ

 

[lameid]

I didn't see your post in the queries forum so maybe you didn't cross post. Generally, you should make a guess, post in one forum and if you don't get an answer in a reasonable amount of time, post in the other. If you do cross post, you should reference the thread. Every thread has the word thread and a number after it. If you copy that and paste it, it will show as a thread link.

That said, it sounds like you are confusing reports and forms. Maybe not, but Reports are generally used to display data, especially for printing. Forms are usually used for data entry but can be used to review data. They are very similar in the way they work in design view at a basic level. You can have subreports just like subforms. Both can be based on queries. Reports have grouping features that forms do not. You can use tabs on forms and you can't on a report. Forms are better at showing and navigating information on a screen, reports are better at showing information on a piece of paper.

I have tried to put a criterion in the queries on the "Delete Date" field so that the quries will not display the deleted records (I have tried "is null" "is empty" and "is missing") but when I run the query with these parameters I get no records back.

Assuming Delete Date has a datatype of Date/Time, is null should work. If it is not that datatype, what is it and why? (Maybe you should change it).

In the form I have a command buton which runs a macro that runs the qury and then update dthe subform. Is there a way to get this button to run different macros after different events in the form or do I have to add a button for every specific task I want.

Yes with advanced VBA techniques, this is possible but for now, I recommend solving one problem at a time and using individual buttons. Once everything is working post again or repost here.

is there a way to place all three result subforms in the same spot

Tab control?

I want to create an update query that will draw its data from Single-subform (the form which displays the results of singleRecQuery). I have created the qury without a problem but the fields in the form are locked for entering data (maybe because they are bound). Is there any way to enter data in such a form which is based on a query and then use the new data in another query ? (I tried allowing data entery in the forms properties but it deos't work).

This is the crux of me mentioning reports... It sounds an awful lot like you have the same base tables under all these forms/subforms. Access gets pretty fidgety when it comes to doing this and updating data. Based on your comment about the form not being updatable, I suspect your query behind it is not updatable. It could be something else but open the query and see if you can make changes. If you need help with the query, change the view to SQL and copy and paste the SQL in this thread.

 

[ejoffe]

Hey Lameid,

First, sorry for double posting - won't happen again.
Second, I am really greatful for your reply - it is very kind of you.
I am afraid I cannot use reports as my assignment is to create an interface for the user which allows entering, querrying and displaying the data. It probbaly should have been implemented in a way other than access (maybe c# or java) but I have no experience with such.
So, I would like to refrain from having many different tabs on the interface for displaying the results from the diferent querries and for entering the data. My question therfore remains:

Is it possible to enter data into a querry from a form that is in itself bounded to a different querry (because the field names are the same)?

Is it possible to display in the same space on the form different subforms in a way that each will become visible only after a certain evnt in the main form (like pressing a command button) ?

I thank you again
EJ

 

[lameid]

Is it possible to enter data into a querry from a form that is in itself bounded to a different querry (because the
field names are the same)?

You could use an append query to add records to the target table by using either the same table/query source and criteria OR reference every field on the form instead of using tables. Similarly you can do something with update queries if you are changing records. In a query, you can right click and select build and navigate the panes from left to right at bottom to select controls on forms... The syntax is the same if you use VBA and reference another form.

Is it possible to display in the same space on the form different subforms in a way that each will become visible only after a certain evnt in the main form (like pressing a command button) ?

Yes. Just stack the subforms in the same place and use code to make them all invisible. For example:

Sub FormsInvisible ()
   Me!Subform1control.visible = False
   Me!Subform2control.visible = False
End Sub ()

Then on the forms on open event call FormsInvisible and on every button that changes the visible form.

On the button that makes a form visible set the form you want visible in the same way except use true.

Once you get that working you may consider putting code on some other event. I suggest you also lookup the Select Case statement while you are in a code Window.

 

[ejoffe]

Hi Lameid,

I almost done (thanks to you).

The querry problem, however, remains or maybe I did not understand you.
I am trying to use a subform as the source of an update querry but the problem is that this subform is actually bound to a different querry all together. I am doing it because the field names are the same.
So I have defined all the links but when I try to type data into the form it is impossible to type anything (the form displays the result of the querry it is bound to and won't accept any new data). Anyway to get around it or maybe i'll just stack another form at the same place.

Thanks a lot

EJ

 

[lameid]

Oh your problem is that you can not update the data in the subform not updating some other table...


Try opening your query and typing data into it. If you can, check the enabled and locked properites of controls on your subform. If not, your query is not updatable for some reason. Obviously if you do grouping it is not updatable. Maybe if you post the SQL for the query something would jump out at me.

 

[ejoffe]

The query is with grouping - You gave me my asnwer.
Any ideas how to overcome it ?
Thanks a million
EJ

 

[lameid]

Don't group.

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886