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

Access - Query when DataSelected

P: 15
Hi, I have a Form, with a SubForm as Continous Form in DataSheet.

Main form: frm_bitacora
SubForm: sfrm_bitacora

When I select a row from sfrm_bitacora I have the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Click()
  2.     MsgBox Me.id_report.Value
  3. End Sub
So it shows me the value from the fieldname from the table "tb_machine".

Now in my main form, in the footer detail, I have some fields that are from tb_report.

What I want is when I select the value from the datasheet, in the footer detail change the values as a query, I mean, I want to return the values from tb_report depending on the selected data.

Note: tb_report my primary key is "ID_Report".
Note: tb_machine my primary key is "Machine_name", but I also have a column named "ID_report" in my logical mind I made this to connect both tables.

This is an Image.

http://imgur.com/7PNoldg

Mar 3 '15 #1

✓ answered by twinnyfo

First, your "concept" of adding ID_Report to tb_Machine is not a bad one. So, your are thinking in good directions. However, from a DB principles perspective, it creates redundant data, which is always a no-no....

I will give you an example to show what I am trying to do, because the result is the same:

Machine1 goes down. Its status changes from Operating to Off Line. You need to open a new report. You currently have three different reports for this machine:

Expand|Select|Wrap|Line Numbers
  1. ID  Machine  Date        Status
  2. 17     1     2013-12-25  Repaired
  3. 32     1     2014-08-14  Repaired
  4. 75     1     2015-01-01  Awaiting Parts
Based on our previous conversations, we COULD do all the reporting with one Button ("Report"). In the example above, you would create a query that finds all reports associated with this machine that are still open (which would be 75). Since there is a record like this, we open ID_Report = 75. let's say 75 had a status of Repaired (i.e. the machine is back on line), then your code would go to a new record for Machine 1.

Yes, according to your method, all you would have to do is check to see if there is a report ID associated with Machine 1 in tb_Machine. In one sense, this is "easier"--and I don't deny that. However, design-wise, you could run into problems if you have thousands of records--that is thousands of bytes of extraneous duplicate data. I know you don't have thousands of records, but this is the concept.....

All we need to do is just filter a form based on the Report table to the specified record. It can either be a subform on your main form, or a main form. It may be best as another subform. Everything can be done relatively easily, once you get the forms properly designed and related.

Share this Question
Share on Google+
17 Replies


jforbes
Expert 100+
P: 1,107
I'm not a 100% sure of what you are asking. So I'm guessing you aren't getting the results you want from your MainForm and SubForm setup.

What I would do in this case is to flip your SubForm and MainForm, by making your MainForm the Continuous Form and displaying the MachineName and Status. Then add a SubForm, in the Form footer, that is a Single Form displaying all the information associated with the selected record from the MainForm. This would allow you to use the Built in Linking that Access provides and to the user it would function nearly identically to the picture you provided..
Mar 3 '15 #2

P: 15
@jforbes
Yeah, I understand what do you mean by doing that, and it also correct. But, what I am trying to make is a Program where I can change the status of my machines, for example, I have my machines Online, and I can see them in my datasheet.

When one machine is down, I selected it from my datasheet, and then I would create a button like "Edit" then I will say the failure that cause, and then when I saved it, this machine would refresh and it wont be anymore as Online, now, the status is "Mechanic on the Way" so in my datasheet, it won't appear, I will need to filter the machines that are in "Mechaninc on the way" so I can see them in my data sheet.

That's my Goal.
Mar 3 '15 #3

twinnyfo
Expert Mod 2.5K+
P: 3,484
It seems to me like you are overcomplexifying a very simple solution.

Why not just add a field to your list of machines that indicates the status. You update the status, then filter based on whatever status you are interested in viewing.
Mar 3 '15 #4

P: 15
htt p ://i.stack.imgur.com/EL9LF.jpg


I only need that when I click on a row, depending on the ID_Report it has, my fields get the values from the Id_report selected.
Mar 3 '15 #5

twinnyfo
Expert Mod 2.5K+
P: 3,484
It would be very helpful to know the structure of your tables involved here.

It is difficult to tell what you are trying to do from either your descriptions or your images (which are identical).

It is also difficult to understand how the two tables/forms are supposed to be related.

Again, if the Machines have a particular status (e.g. "Operacion", "On Line", "Off Line", "Mechanic On the Way"), then that status should be a field in the Machines Table.

The bottom of your main form show 35 additional fields. Are these from the Machine Table or the Reports Table?

Just on the surface, depending upon exactly what it is you are trying to do (which is still unclear) it looks like a restructuring of your tables may be needed.

We will need much more detail on what you are trying to do and how you are trying to do it before we can move forward with any recommendations.
Mar 3 '15 #6

P: 15
Ok:
I Have 3 tables:
this are my Relationships.

http://imgur.com/Mctu89d



This is my design form of my frm_bitacora

http://imgur.com/xCgDULb



We can see in my MainForm, that I have a combobox, and a subform, in the part below in "Form Footer" I add all the fields of my table "tb_report".

What am I trying to do?
When I open the main form, the combobox it is fill thanks to a SQL Sentence:

SELECT tb_status.Status
FROM tb_status;

if we check our table Status, it brings all the status (in my language) translating to English is "Operating" "Mechanic on the way" "Electronic on the way" "In Repair" etc...

so when I open the form load it runs the next code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_status_AfterUpdate()
  2. Me.sfrm_bitacora.Form.RecordSource = "SELECT tb_machine.machine_name, tb_status.status, tb_machine.lastupdate, tb_machine.ID_report " _
  3.     & "FROM tb_machine INNER JOIN tb_status ON tb_machine.Status = tb_status.ID_Status " _
  4.     & "WHERE tb_status.Status = '" & Me.cmb_status & "'"
  5. Me.sfrm_bitacora.Form.Requery
  6. End Sub
  7.  
  8. Private Sub Form_Load()
  9.     Me.cmb_status = Me.cmb_status.ItemData(0)
  10.     cmb_status_AfterUpdate
  11. End Sub
in other's words, the form load fills the combobox with the 1 ItemData, and then it calls the "AfterUpdate Method". This method makes a sentence that brings all my machines from tb_machines, with their actual status... and it shows only "Operating" because its the 1 ItemData.

If I change in my combobox to "Mechanic on the way" I wont see any machines on my datasheet.

Ok so... now I pretend to change the status of my machine, imagine the machine got a failure, so now they report that Machine01 its down..

I selected the machine01 and click on button, this will bring the fields Editable and i will fill them as the time it got down, maybe what was the problem, and that. and CLICK SAVE.

This will change my status depending on which problem i selected, in case i select Mechanic, the status will be "Mechaninc on the way". When the Mechaninc arrives to the machine, again i will select the same machine, and click edit again, and now i will fill the time that the mechaninc arrive.. and click save... when this is filled, my status of the machine will change to "In Repair".

Now the main problem is ... how ... when i select my machine from the datasheet, how can i edit the id_report before created i doesn't have linked my form footer with my subform.
Mar 3 '15 #7

twinnyfo
Expert Mod 2.5K+
P: 3,484
Much better explanation of where you are starting! Thank you.

To begin with, a recommendation, based on good DB structure and principles. Your Table tb_status has a PK (ID_Status) and a Status. Assuming that this PK starts with 0 -- which I recommend to be your "default" value for your status -- then there are some ways to bring the rest of your structure and code more in line with some good principles.

First, change the Row Source of your combo box. The query might better be:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tb_status ODER BY ID_Status;
The reason being that Access can much more easily use an index than it can a string value. Thus, your row source will become:

Expand|Select|Wrap|Line Numbers
  1. Index   Value
  2.   0     Operating
  3.   1     Mechanic on the way
  4.   2     Electronic on the way
  5.   3     In Repair
  6. etc....
In principle, then, a "status" of 0 is your default, "no problems, everything is fine" status for your machines. You must make sure that your combo box now has 2 columns, and you can hide the first column (so that only the values are shown). This is a standard, well-proven DB principle, so I highly recommend this method.

Now, when you load your Form, slight mods to that code begets:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.cmb_status = 0
  3.     cmb_status_AfterUpdate
  4. End Sub
This will yield the same results that you had before, but the code is "better" because you are looking at an index, and not a text string value.

Then, to modify your code in the After Update:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_status_AfterUpdate()
  2.     Dim strRowSource As String
  3.     strRowSource = "SELECT tb_machine.machine_name, " & _
  4.             "tb_machine.status, " & _
  5.             "tb_machine.lastupdate, " & _
  6.             "tb_machine.ID_report " & _
  7.         "FROM tb_machine " & _
  8.         "WHERE tb_machine.status = " & Me.cmb_status
  9.     Me.sfrm_bitacora.Form.RecordSource = strRowSource
  10.     Me.sfrm_bitacora.Form.Requery
  11. End Sub
Note, first, how I established a string variable to hold the SELECT Statement. This is a good practice so you can evaluate your variable, if needed, before it is assigned to the property.

Also note tha6t there is no need for a JOIN, as the Machine Status has a Foreign Key to the tb_Status PK. Just use the index. It is the right way to go.

Before we go any further, I have to ask about the Report Table. It seems that this table holds data concerning the machines, yes? Do you keep a running history of all the service calls on your machines? If so, then this will require some other tricks. However, If you do not need a historical records of the status updates, then these fields could all go into the Machine Table.

However, you probably "should" maintain a historical record of your work. But.... There may be different ways of doing this.

More to follow after I learn more about how you are using your data.
Mar 3 '15 #8

P: 15
@twinnyfo
Reading this, give me a second, Working on the changes.
Edit: Still Editing, so many errors i got when i change my Status, but still editing.
Mar 3 '15 #9

P: 15
Ok i still have some errors, my tb_status is already edited.

My PK ID_Status it was Autonumber i change to Text and manually i used, 0, 1, 2, 3 ... etc.

I change the properties in my combobox so it only shows Column 2, that are the description.

i Added to the form_load "cmb_status = 0" and this brings me a 0 when i load the form, but no the description... that is my first error.

""EDITED"" i resolved this problem, now it brings the correct description, in properties i had to select bountycolumn to 1.

and about your question, this is what i am creating to maintain a historical record, here i will see how many downtimes it has each machine and what is the total downtime of each machine.
Mar 3 '15 #10

twinnyfo
Expert Mod 2.5K+
P: 3,484
Details:

My PK ID_Status it was Autonumber i change to Text and manually i used, 0, 1, 2, 3 ... etc.
There is no need to make this field Text. Keep it as an integer, as numbers are easier for the DB to work with. When you have a lot or records, always use the AutoNumber as your PK. This allows you to add and remove records without worrying about what the PK actually represents, as it is merely an index for the DB to use for finding records and using elsewhere. However, when you have a small list of items (that you are going to use as a look up field for another table--such as you have), always set the PK to Integer. In practice, if I have one of these and there is a standard "default value" for that field, I always set that value first, make the Index a 0 and then, in my table, I have as my default value for that field 0. Then, any new records that are created will be set to the default value.

Using your example, if the Machine Status had a default value, any time you create a new machine record, the default status would be 0, meaning, "Operating". This will almost always be the machine's status--so it makes sense. The only time you change the status is when something goes wrong.

Likewise, when I have a record that must go through several processes, with a status, I set the default to "New", for example. Then, I cycle through that status as "Step 1", "Step 2", "Step 3", etc. These are just some good principles to get into practice.

Next point:

and about your question, this is what i am creating to maintain a historical record, here i will see how many downtimes it has each machine and what is the total downtime of each machine
In this case, I would refer back to jforbes advice:

What I would do in this case is to flip your SubForm and MainForm, by making your MainForm the Continuous Form and displaying the MachineName and Status
You have some challenges in how you are going to do this, as the status is associated with the Machine Table, but the reporting fields are in another table. Additionally, I presume you want a separate report_ID for each time the machine goes down?

As I think about "how" to do this, I would recommend the following:

Your Main Form should be based on the Machines Table; it should be in Continuous view--not datasheet. It might also be best to have a plain form, and have your continuous form embedded as a Subform. For your continuous Form, add two small button to the right of each record "Update Report" and "New Report"--or something to that effect. These buttons will have similar functions but different purposes.

The "Update Report" button would filter another SubForm (which is based on the Report Table. This filter would bring up the most recent report you have on this machine. This could just filter all reports on that machine and you update as necessary, but considering the number of fields displayed, it makes more sense to show just the most recent. If there is no record, then this would create a new record for this machine.

The "New Report" button would just create a new record.

You should be able to update the status of each machine, as needed, and of course the main form should also be able to filter all your machines by current status, which I think you essentially have the first parts of this nearly complete.

I hope this made sense. Standing by for additional assistance.
Mar 4 '15 #11

P: 15
@twinnyfo
Ok, I understood everything, I will need to re-structure my program, this may take a little while, about the continuos form, what is that?

I read something in google, but still isn't clear, I though that my datasheet was a continuous form.
About your buttons you mention, Yes! you are correct, this is something I was having on mind, but as you see I represent it a little bit different.

I am thinking you are talking about some checkbox next to the field, but I would like to make it more easy because the user who are using this, are "operators of machines" we are talking that they might not even know how to use a computer (don't ask me why haha) but is something normal, so we must create easy thins for them instead of complicated.

So I was looking for the datasheet, but in this case you mentione a continuos form, can you show me, what do you mean by this?

"EDITED"
I think I will focus on re-structure following all the recommendatios you just gave me, so what about if I have another issue, I could contact you.
Mar 4 '15 #12

twinnyfo
Expert Mod 2.5K+
P: 3,484
Continuous Form:

Create a New form with a Form Header/Footer. Record Source = tb_Machine. Default View Property = Continuous. In the Header, add the labels for your Fields. In the Detail, Add text boxes for your fields. Make the height of the detail equal to the height of one text box and place all text boxes in a row, horizontally. Add a Button for Update Report to the right of the fields. Add a button for "New Report" to the right of the other button. When you view the form in normal mmode, it will list all of your records as a list (looks a lot like datasheet view, but it is a form). When you place this form as a subform in the main form, you can adjust the height of the subform so it only shows as many records as you want (3, 5, 12--however many you want).

I always recommend making DBs as easy to use and as gorilla-proof as possible. It should be easy for the user to understand.

Concerning additional issues, please feel free to begin a new thread. Others may also have great ideas for assistance.
Mar 4 '15 #13

P: 15
@twinnyfo
Here you will find what are you talking about:
https://www.dropbox.com/s/vhnoys8oso...orm.accdb?dl=0

this is my example before the datasheet, I was using Continous Form in the right way, just someone in another form told me that it wasn't continuous form.

There you can see my form - frmStatus, as my continuous form, so when I call frmMain, my subform is frmStatus, so is it show as a datasheet.

Why would I leave this example and start another, because I though that having a continuous form as my subform was incorrect, but again I see was in a correct way.
The only problem here it was when I select a machine, from the subform, I could get the value but I couldn't match it to the "id_report" to call back all the fields of that value I get.
what I mean is..
Select Machine01 - with the id report = 1, when I have this selected I click on "cambiar" and I wanted to bring it up in all my fields, the fields of my tb_report.

Again, I was all confuse.
Mar 4 '15 #14

twinnyfo
Expert Mod 2.5K+
P: 3,484
I can't access DropBox from work......

Based on your first images, your form was, in fact, in datasheet view.

The problem with Selecting Machine01 and getting ID_Report = 1 is that there should not be an ID_Report in tb_Machine. That is a backwards structure from what you want (which is ID_machine in tb_Report--which is what you have and is correct).

The real confusion will come when you have a machine that goes down. How do you determine which ID_Report to use? This is where you must have a list of Report IDs that have been started on the correct Machine ID. If there is a Machine ID in the reports Table already, is that report closed out and is this then a new report? These are the challenges that you will face--all of which can be overcome. But, these are things you must keep in mind.
Mar 4 '15 #15

P: 15
@twinnyfo
If you could send a PM with your e-mail I can send it to you, and YES. That's the real confusion, i'm over there.
Just right There!

That's why I added Id_report in my tb_machine.

This id report will only be updated when a new report is created for that machine.

For example all my machines are at status = 0, when my machine01 change status, I can't create any new report until that machine changes to status = 0 again, so meanwhile that id_report will be saved in the tb_machine.

When status = 0 again, the id_report from tb_machine will be wiped out, so when I new report come in, that field will be fill with the new id_report, that's my link.

The problem is when I need to change, how can I call this id_report and when I open my new form bring me all the fields from the tb_Report depending on my id_report I took from tb_machine.

I know I might be taking a lot of your time, but you are the only one helping me, please, do not abandon me :(
Mar 4 '15 #16

twinnyfo
Expert Mod 2.5K+
P: 3,484
First, your "concept" of adding ID_Report to tb_Machine is not a bad one. So, your are thinking in good directions. However, from a DB principles perspective, it creates redundant data, which is always a no-no....

I will give you an example to show what I am trying to do, because the result is the same:

Machine1 goes down. Its status changes from Operating to Off Line. You need to open a new report. You currently have three different reports for this machine:

Expand|Select|Wrap|Line Numbers
  1. ID  Machine  Date        Status
  2. 17     1     2013-12-25  Repaired
  3. 32     1     2014-08-14  Repaired
  4. 75     1     2015-01-01  Awaiting Parts
Based on our previous conversations, we COULD do all the reporting with one Button ("Report"). In the example above, you would create a query that finds all reports associated with this machine that are still open (which would be 75). Since there is a record like this, we open ID_Report = 75. let's say 75 had a status of Repaired (i.e. the machine is back on line), then your code would go to a new record for Machine 1.

Yes, according to your method, all you would have to do is check to see if there is a report ID associated with Machine 1 in tb_Machine. In one sense, this is "easier"--and I don't deny that. However, design-wise, you could run into problems if you have thousands of records--that is thousands of bytes of extraneous duplicate data. I know you don't have thousands of records, but this is the concept.....

All we need to do is just filter a form based on the Report table to the specified record. It can either be a subform on your main form, or a main form. It may be best as another subform. Everything can be done relatively easily, once you get the forms properly designed and related.
Mar 4 '15 #17

P: 15
@twinnyfo
Thank you for all your help, I keep working on it, I do what I could at my hands, I know I have a lot to learn, but for now I got completed the thread subject.
Mar 5 '15 #18

Post your reply

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