Access - Query when DataSelected

[Oflores23]

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:

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. htt p ://i.stack.imgur.com/EL9LF.jpg

 

[Oflores23]

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:

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. htt p ://i.stack.imgur.com/EL9LF.jpg

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.

 

[Oflores23]

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.

 

[Oflores23]

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

(Sorry I can't Post LInks".)

h t t p : / / Imgur.com / Mctu89d

This is my design form of my frm_bitacora

h t t p : / / 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.

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. Private Sub Form_Load()
8. Me.cmb_status = Me.cmb_status.ItemData(0)
9. cmb_status_AfterUpdate
10. 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.