Combo box update text box on subform and table

Question

Hi, I have 2 tables, Services Calls (Include Call #, Date, Status, etc.) and Events (Call #, Event Date, Status, etc.) with same name on forms. On form Events we have a combo box with 2 options, Open or Close for events status. On this form we have a sub-form of Service Calls(Call #,Status), if on the Events form status combo box list selected are close show the status text box to close in the sub-form name Service Calls, update the status record of call # in Calls table, if on the Events form status combo box list selected are open show the status text box to open in the sub-form name Service Calls, update the status record of call # in Calls table . I try to use the following code

Private Sub Cbo_Status_AfterUpdate()
    If Me!Cbo_Status = "Close" Then
    Me!Txt_Status = "Close"
    End If
   
    If Me!Cbo_Status = "Open" Then
    Me!Txt_Status = "Open"
    End If
    End Sub

Answer 1

Hi,

Not sure I understand your question. Are you trying to "update" the data in the subform or are you trying to "filter" it?

Answer 2

Ok, Let me explain, on this database the first procedure is go to the form "Service Calls" to add new record (Example Call #3) remember the table have the field names Call #, Date, Status, etc. and on the field Status" I selected on the Status combo box "Open", then go to the form Events (Remember have field names Event Date, Call #, etc.) on Events form and  on sub form  have the field names Call #, Status, etc., and after create a new record related of Call #3 if I select "Close" on the combo box in the Event form, we need show "Close" in the status Text box on the sub form and change the value of the Status in the Call #3 to "Close" on the table Services Calls.

Answer 3

Hi Miguel,

Based on your description, I am not sure whether you get error when you implement your requirement or you want to achieve this requirement.

I would suggest you share us a simple database which will have your current Tables, Forms and events. With these, share us what you have achieved and what you want from current simple database. A screen shot description would be much helpful.

Best Regards,

Edward

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Ok, Let me explain, on this database the first procedure is go to the form "Service Calls" to add new record (Example Call #3) remember the table have the field names Call #, Date, Status, etc. and on the field Status" I selected on the Status combo box "Open", then go to the form Events (Remember have field names Event Date, Call #, etc.) on Events form and  on sub form  have the field names Call #, Status, etc., and after create a new record related of Call #3 if I select "Close" on the combo box in the Event form, we need show "Close" in the status Text box on the sub form and change the value of the Status in the Call #3 to "Close" on the table Services Calls.

Hi Miguel,

Are you saying the Service Calls form stays open when you open the Events form? If so, maybe you could just requery it. Seeing at least a screenshot would probably help us understand the setup better.

Just my 2 cents...

Answer 5

screenshot

Answer 6

Hi,

If you're storing the Status information on more than one table, you may be risking data integrity. It's one of the rules of Normalization. In any case, if you're showing two separate forms, then you could update the status on one table or the other by using an UPDATE query. And, if you're viewing the two forms at the same time, you need to Requery them after you execute the UPDATE query.

An example of an UPDATE query is something like this:

UPDATE tblEvents SET Status="Close" WHERE RecordID=82

Does it help?

Answer 7

Yes, Thank

Answer 8

Hi,

Good luck with your project.