Solved Dialog form, temp table transfer (1 Viewer)

[mib1019]

Hi all,

I am attaching a small db file to get some help with a new approach I am trying to take with my project. Currently, I have the user doing input to a similar dialog with a subform of dates (some of you have been a big help on that form). Anyway, he's finding that a little cumbersome, so I'd like to give him a quicker option, clicking with his mouse or finger on the list box rather than writing data.

The dates collected need to be added to a table called tblIODetails_Dates. I thought the best approach is to collect his lstChooseDates input into a temporary table which is created when the dialog opens, and when the user finishes selecting dates in the lstChooseDates and closes the dialog, those records in the temp table are appended to the permanent tblIODetails_Dates table, the txtResult contents get written to the calling form and the temp table is deleted.

My first issue is that TempDates table isn't always created on opening Form1. It opens properly when I first open the database (Form1 auto-opens), and from form design only. When I open Form1 from the Navigation Pane, the TempDates table isn't created. Don't know what's going on there.

You won't have this issue when you open my little test db, so, you can input the start and end dates, and some Detail_ID, then click the LIST Dates button

Next issue: As you make listbox selections, I need the txtResult to format the dates simply as 'm/d' (for the sake of room). Can't figure out how to do this.

Finally, how to get the selected dates, along with the Detail_ID to append to tblIODetails_Dates table. The Insert into the permanent table needs to make sure it is not writing a duplicate Detail_ID and Air_Date, if the user has come back to add a date missed on the first pass and reselects previously selected dates.

I may be opening a can of worms here. The listbox needs to populate with all dates between txtStartDate and txtEndDate. Could the listbox show previously recorded dates as 'selected' when the list populates?

Any help you all can give me with this would be greatly appreciated. If there is a better approach, I'd welcome that too.

Thanks!
MIB1019

 

[June7]

I would make temp table permanent but records temporary. If this will be a split database, put temp table in frontend. Instead of CREATE TABLE (and don't delete table):

Private Sub Form_Open(Cancel As Integer)
CurrentDb.Execute "DELETE FROM TempDates"
End Sub

Set compound index on Detail_ID and Air_Date fields to prevent duplicate pairs.

Yes, code could set items in multi-select listbox as selected. Check against dates previously recorded where - tbllODetails_Dates? (do you really want double "l" in table name?)

 

[Pat Hartman]

Deleting rows and refilling causes the same bloat that make tables cause.

 

[arnelgp]

here is your Listdemo, no need for temp table.

 

[mib1019]

Thank you everyone. Looks like it will work. Working on other aspects right now. Will keep you posted if any further questions...I suspect there will be

 

[mib1019]

Another wrinkle. My user wants easy easy input. Here is a wrinkle.
Sometimes the Dates listbox needs to fill with Week starting dates, rather than daily dates. I've got that part working on the little form we've been addressing.
Where the listbox fills with dates, there would be another field to enter days of week. These are days to air given the given week.
He wants view his input as per the following examples. What would be the easiest way to accomplish this?

Start Date	Weekdays​
08/17/20​	MTWTFSS​
08/24/20​	M-W-F--​
08/31/20​	MTWTF--​
09/07/20​	-T-T---​
09/14/20​	-T-----​
09/21/20​	-----SS​

 

[mib1019]

Hello again.
Let's forget that last post of mine, about the wrinkle. Got that working properly.

Back to the airdates listbox. It works correctly to add the dates to tblIODetails_Dates, filling the new rows into the table after the user finishes selecting dates in the listbox on the dialog.

Now what if I need to edit the list of dates? When the calling form loads the listbox with dates between start and end, I'd like the listbox to show the existing dates 'selected'. If I need to delete a date, I unselect, and it should be removed from the table. If I reselect it, it adds it back in. If I select a new date, it adds that too.

Thanks for all your wonderful help!
MIB1019