Dynamically update Subform datasheet by its list box

Question

Hello

When I select a record in my database and hit the Task Notes button, it opens a subform TaskSubform showing the datasheet for the notes for that record. Here’s the situation:

The entry field defaults to value =now() for new records. If I add a note on the current record, it allows me to do that and it also allows me to click a list box option and also edit that one. however, if I open It and instead of making a note for the current record I first select one from the list box, then I get an error that I must enter a value for Task_NotesT.Entry field.

If I go to a record that does not have any notes yet, I can add a note but I also need to select the LoanID in the datasheet to complete the note (which is fine), however then in this case I CANT navigate to a new record without getting the same error.

I want to be able to open it from any record and be able to add notes to any record I select in the list box. Not sure how possible it is to make this happen without making it extremely complicated.

Finally, I want to create another section but for my “leads”. When I close a deal I want to see a query of closed homeowners associations as a list. However I want to be able to select filters from an option group; closing in 10 days, closed within last 10 days, closed 45-60 days ago, closed 90-120 days ago. I want this selected filter to apply to the list box of associations, and when an association is selected in the list box is populates those records in a datasheet. I want it exactly like this notes form, but imagine there’s a few buttons for filters on the side. I can handle the criteria part of the filtering, but I have no idea how to make the list box change based on a filter in an option group selection.

Thank you for reading this and please ask questions if they will help you help me.

Answer 1

That's a busy form, but I Iike the way you have integrated embedded subforms. Using Datasheets for embedded subforms, is fine, since you don't really need the extra controls that a continuous form would give.

I'm guessing this database is more for your own use rather than sharing with others. If that is the case, it is important that it is comfortable for your use.

Answer 2

OK, , So this is not quite the way I would do things. So the button on the main form would have code like the following behind it.

DoCmd.OpenForm "popupformname",,,"LoanID = " & Me.LoanID

That would open the Popup form for Notes for the Loan currently displayed in the Main form.

On the Popup form, I would not use Datasheet view. I would use a Continuous form view so you can use have greater control over your data entry. This way you could have a larger text box for the Notes field for example.

The next thing I would do is put a combobox (you can use a listbox if you need to see more info about each loan) in the form header. This control could be filtered for the loans as indicated. I would, then, use the following snippets of code:

In the On Open Event I would use

Me.controlname = Me.LoanID

This would set the control in the header to be the LoanID in the calling form.

Then, in the After Update event of that control I would have code like:

Me.Filter = "LoanID = " & Me.controlname

DoCmd.RunCommand acCmdApplyFilterSort

So, after you select a new Loan in that list control, you will now see Notes for the loan selected.

I would also set the Default Value for the LoanID control to

Forms!popupformname!controlname

So new records would pickup the LoanID from the list control in the header.

That should allow you to add new Notes for different loans from the popup form.

OK, For the Option group issue since you want a range of days, Then I would use code like the following in the After Update Event of the Option Group.

Dim strSQL As String

strSQL = "SELECT field list FROM table "

SELECT Case Me.optiongroupname

Case 1

strSQL = strSQL & "WHERE CloseDate < Date()+10;"

Case 2

strSQL = strSQL & "WHERE CloseDate > Date()+10;"

Case 3

strSQL = strSQL & "WHERE CloseDate BETWEEN Date()+45 AND Date()+60;"

Case 4

strSQL = strSQL & "WHERE CloseDate BETWEEN Date()+90 AND Date()+120;"

Case Else

strSQL = strSQL & ";"

End Select

Me.controlname.RowSource = strSQL

Me.controlname.Requery

Answer 3

Hi HelpMeMs, I will try to help.

I need more info to help. Is this a popup form? Generally when you refer to a subform, its embedded on a main form. What does the list box allow yo to select, you did not make that clear. Is there any code behind the list box that might be causing the issues? You stated, that the Task subform shows a datasheet with the notes for the selected record in the parent form, but then you said you want to add Noted for any record? Wouldn't it make sense to go back tot he parent and select a new record?

You refer to LoanID, is that the primary key of the Parent Record that identifies what record the Notes are for?

If you want to filter a list box, based on options in an Option group, you have to either set criteria for the Rowsource of the list box that references the Option group, or reassign the Rowsource as a filtered SQL statement. To advise further we need to know more about your table strucutre and the Rowsource of the listbox.

Answer 4

This is a fantastic response. Thank you. I haven’t played around much with continuous forms but I think that actually would work better. I’m going to play around with it a bit and see if I have anything come up. But the logic around what you said for how to make it do what I want makes sense.

I think it might be good enough the way it is, since main loan notes are on the main form and these are like sticky notes, however I agree it would be better as a continuous form for any changes I may want in the future. so I will do that.

I think this will lead me to change more in the database. This leads into the grand purpose of why I’m doing this. I’m taking the approved associations and using the agents listing properties in that association as leads.

I have another datasheet setup for the listings and additional information for property-listing and agent information as shown in the pic. I think I should also change this to a continuous form but thought while we are here I’d see if you had any comments.

I’m not a tech guru and I’m going off what I learned in access in college so there could be better ways to do what I’m doing. Note the contact information here is public record and thought it’s safe not to omit

Answer 5

Hello. Thank you

Yes it is a pop up form, you are right this is a form that pops up from a button on the main form.

Loan ID is the primary key of the main table.

The list box allows you to select different loans. It is filtered so that only loans submitted, Approved, In Review, In Underwriting, and in Closing Review are shown there.

I am constantly going back and forth between files as I collect documents or take calls about them and I think this is the best way to keep my notes.

There are no code on the list box or the task form, only for the button to open the form. Not sure if this affects it; There is a button that locks the main form unless it is clicked, changing records relocks an unlocked form, and new records can always be added.

Yes, I could go back to the parent record and change it. It’s more of a convenience/user-friendly thing, since I’m working multiple files I may want to glimpse at a note for one file while I’m working on another or take a phone call about one file while the other is open and make notes on the fly. But it is a convenience thing and isn’t necessary if it makes everything a huge mess to make possible, especially because it’s only me using the database.

Yes, the main form LoanID is selecting that loan in the datasheet by default when I open the task/Notes form. This is ideal though, as most often I will only want the notes of the file I’m on. Which is why it would be nice for the “LoanID” in the datasheet to automatically say that LoanID even if There’s no notes yet (could I just set the default value to =forms!FilesForm.LoanID? (Rookie at coding).

For the option group question, hope this helps:

The list box would be a query with HOA name, the type of financing it approved for, and the close date. The close date would be the only thing filtered by the option group. So I’d want the items in the list box to be filtered dynamically when I change selected options. When you say set criteria for the row source of the list box, you mean I could make criteria some code that says for example if option group = 1 then criteria = today+10?

Thanks again. Not sure if this helps but I think I can attached a blank template database here, if you don’t see it, I failed.