How to get the subform to refresh after entering data on a dialog form

[devlearner]

Working with Access 2013
I have a subform that is part of the main report form. I want users to see entries which they would enter using the dialog form (that should be connected with this subform). The subform is in Datasheet view since there are many 'Compliance' associated with the report.
I tried using a DoCmd.Requery in the AfterUpdate and also the form load.

Don't know much of VBA code but someone told me it's one line, yet I am waiting weeks for this one line.

How can I get the datasheet to update after the dialog entry was submitted?

[zmbd]

devlearner
would be most helpful to post your code so we can see exactly what is happening - be sure to use the [CODE/] formatting option...

If you are calling the dialog form from within the Subform's code, AND, the dialog is "Modal" then the easiest method is on the line immediately following the call to your dialog is to use Me.Rquery

So your code might look like

(... code in the subform ...)
DoCmd.OpenForm FormName:="Form'sName", View:=acNormal, WindowMode:=acDialog
me.Requery
(... the rest of your code in the subform ...)

If you calling this from the mainform then you need something like

Me.SubformCtrl.Form.Requery
"SubformCtrl" is the name of the control that holds your subform. A default unbound Subform control's name is "Child#" where the # is somewhat arbitrary.
The default name of the bound Subform control if the child is dragged and dropped is the subform's name - I HIGHLY advise changing this to avoid confusion

The Me in each case refers to the calling form.

A more specific answer can be provided if you can post your code or be much more specific on how the dialog form is being opened.

[devlearner]

Hi zmbd

I do not have any VBA code for the forms.
For the subform inside the main form, there exists a tab control and this subform resides on one of the pages.
This subform is datasheet view and suppose to reflect data entered on the dialog form. The dialog form is opened using a button with macro control. This was easy for me because I honestly don't know VBA code. My sup told me its a simple line of code but what exactly is that code that will refresh/requery the subform (datasheet) view with the data entered in the dialog form?



This is where I am stuck. Didn't even realize that code was necessary for all these things. I was so accustomed to macros in the past.

I have tried the Me.Requery which was the first bit of code I found online while searching for a definite answer to what I am trying to achieve.

If you need any more information, please do let me know. In the meantime, I don't know how to get this datasheet to update like the backend (master table).

[twinnyfo]

devlearner,

My first advice is to move away from Macros and toward VBA.

Macros can be converted to VBA (open the Form in Design View, under Form Design Tools, in the Design Tab, Tools Group, select "Convert Form's Macros to Visual Basic".

This will get you started. You will find 1) the switch to VBA is not really as difficult as most people think (why MS Access defaults to macros is far beyond the comprehension of any developer) and 2) VBA is so much more flexible and powerful, you will wonder why you never used it before.

I will try to take a closer look at what you have so far. Z is also a very valuable expert here. What you are trying to do does not sound very difficult--we just need to find the details and you should be good to go!

[twinnyfo]

So.... Here is what is a bit confusing:

In image 1 above, the "Sub-Form" (which you say is in datasheet view) appears to be one and the same as frmCompliantdialog (see image 3). If this is the case, I can only assume that the record source for both objects is the same? If this is the case, then why use a dialog form to enter the data?

If the dialog form is opened up from the main form (which it looks like it does), then you will have to call the requery of the main form explicitly from the dialog form just prior to closing:

Forms!frm_Report.frmCompliantDialog.Form.Requery

There still might be a bit of confusion on our part as to exactly how your form(s) are structured.

[PhilOfWalton]

I think it would be heppful (as twinnyfo would say) helpful (as I would say) if we could see an image of your relationships. If you can show the tables fully expanded and not have too much space between the tables, that would be good.

Phil

[devlearner]

Noted.
Honestly, this wasn't my idea to even do a dialog form. I keep having to add stuff along the way because my sup says so. Yes, I am the one developing it but it seems that what you learn in university isn't always applied in the real world.
It's so confusing for me too, to even grasp the logic of why a dialog form that is connected to the same record source.

I gather that the purpose of the dialog form is to prevent users from editing the subform. Also, there are can be more than one Compliant in any report (one to many). I haven't enforced referential integrity.
https://bytes.com/attachment.php?att...1&d=1533219036

[devlearner]

Understood. Trying to find a good starting course online atm

[devlearner]

My apologies for the confusion.

The Report which is the main form has a PK - ReportID which is a part of the other tables as an FK.
Compliant and Report has a one-to-many relationship (ReportID).
I was asked to create the dialog-form within the Compliant and NonCompliant subforms to allow users to create many Compliant/NonCompliant and have it populate in the subforms datasheet view.
My issue is how do I get the dialog form to display the data back to the subform.
I have been trying to ask the right logical questions to grasp some understanding of what I am trying to achieve. I know it is probably simple but I am missing that little thing "simple".
Hope you understand up to there.

[twinnyfo]

This might be really simple, but what is the Record Source for the Form frmCompliantdialog?

[PhilOfWalton]

Thanks for the Relationships

There are a number of points with this, that may not give you problems now, (but I suspect that is why you a having problems with Compliant), but certainly will in the future.

May I suggest
tbl_Location change from ID to LocationID. Then you know what you are refering to. (ID tells you nothing)

In tblReports, delete the field location and add LocationID. This saves holding the location in both tables. Delete the existing relationship between those two tables and create a new relationship between the two LocationIDs. Enforce referential integrity.

There should be a one to many relationship in the ReportIDs in the tbl_Reports and tbl_Compliant. That way you get as many compliants as you want per report.

Delete the table tbl_inspectors_1 from your relationship wind , then add it back. It should now show up as tbl_inspectors (without the final _1).

There is a problem with how you are handling the inspectors, and I suspect there are 3 possibilities. I need to know which one (if any is correct)
1) There is one & only one Inspector per Report.
2) There can be many inspectors for a single Report.
3) The Inspector is something to do with the Compliant rather than the Report.

Incidently, "Reports" is a very bad name as MS Access consists of Forms and Reports as well as other objects. Would "Incidents" be less confusing?

Phil

[devlearner]

Phil,

Thank you and I used your suggestions.
The reason for the other Inspector table (tbl_inspectors_1) this was created by my sup to use as a lookup. Users will be able to select Inspectors via a combo box in the subform.
Yes, there can be many inspectors for a single Report.

I changed the name to Issue (was trying to remember if you had put Issue or Incident oops).
Thus far I left the subforms as datasheets where the users can type in the info and it will relate to the Report ID which is hidden.

Thank you again. I think I'll have to do more research and training before adding more features for the user but at this time, I really want to hand in something that works for now.

[PhilOfWalton]

Thanks for the reply, but unfortunately it may or may not make sense.
The problem is that you say there are many Inspectors per Issue and further on you say that the Inspector can be picked from the Compliant subform. This implies that the Inspector is associated with the compliant.

Although both statements could be true, my gut feeling is that the Inspector is associated with the Compliant rather than the Issue, and if so, can there be more than one inspector associated with each Compliant?

Phil

[NeoPa]

Excuse me for interrupting - but isn't Compliant a boolean item of data?

[zmbd]

devlearner:
I think at this point we may need your database.
If possible create a copy

In the COPY clear your tables

Insert a bogus record or two so that we can see the actual data interaction (John and Jane Doe to the rescue)

Zip the file and attach

There appears to be quite a few issues here (macros vs VBA - shudder), least of which is the [tbl_Inspectors_1] sitting out there in the wild - the fact that it was "needed" for a combo-box is very problematic

Also, please check your Bytes.com Inbox, I'll be sending you a little boiler plate of reference materials.