Create form where I enter several dates with date names pulled from table

[shiraegi]

So I have the following setup:



tblStatements - a table with the following fields:
- StatementID
- StatementName

tblDateTypes - a table with the following fields:
- DateTypeID
- DateType
and the DateType records are a list of various date names such as:
1 Expiration Date
2 Due Date
3 Statement Date
4 Statement Received Date
5 Payment Received Date (etc.)

jctLinkStatementDates - a join table joining the above two tables via StatementID and DateTypeID fields and containing an additional field DateValue which holds dates associated with linked DateTypes and Statements

What I would like to do is create a form where I show a predetermined selection of date names from the DateType records - e.g. Statement Date and Statement Received Date - be able to enter dates for those date names, and both dates should be saved as new individual records in jctLinkStatementDates. So the records in JctLinkStatementDates should say:

(Record ID) (Statement ID) (DateTypeID) (Date Value)
(Record ID) (Statement ID) (DateTypeID) (Date Value)

e.g.

(1) (1) (3) (2/28/2022) [3 being the Statement Date]
(2) (1) (4) (3/28/2022) [4 being the Statement Received Date]

Is that possible?

Essentially, I am trying to have as much flexibility as possible with putting together different lists of date names/types, and with then putting together forms selecting subsets of those date names/types, and for each date entered for the date names/types in the subset to be saved as one record in the join table, linked to the correct date type/name in the tblDateTypes table and to the correct statement in the tblStatements table.

[CJ_London]

certainly possible, it is a common way of doing things.

the form recordsource would be jctLinkStatementDates and you would have the control for datetypeID as a combo, using tblDateTypes as the rowsource. You could use a listbox instead but would not be so easy to use in a continuous form. Typically you would have a main form based on tblStatements and a subform based on jctLinkStatementDates

For reporting purposes, you would just include tblDataTypes in your recordsource and use the datetype field

[shiraegi]

Thanks Ajax for your feedback.

Sorry, I am new to Access as I am hoping to eventually migrate a complex Excel workbook (which has become too big/slow) to Access, have been self teaching me from Youtube classes and have been focusing on properly breaking down and designing the tables and join tables for my database which seems to be getting complex, at least it seems to me (the relationships window looks like a big spiderweb). I am just now getting to thinking about how I will be entering data in cases where I will be entering multiple dates, and I am trying to visualize your comments.

If I drop a combobox on the form, how do I then add a field to pick a date that will be stored in jctLinkStatementDates together with DateTypeID of the selected date name/type? And can I just set a preselected date name/type as a prompt (sort of like a label) if I want the date name/type to be fixed on a form and for the user to just select a date, instead of having to manually select the date name/type in a combobox?

Also, if I would like to have several fields for entering dates on the form, how do I then get them to get stored sequentially as individual records in jctLinkStatementDates (e.g. Statement Date and Statement Received Date)? The sequence wouldn't matter but they would have to be stored in the same table at once...

Edit: It doesn't matter how it looks, I am just looking to get it to function correctly, so if there is a better way to achieve what I am trying to do that doesn't look pretty, I am all for it (I can always refine/adapt later). You mentioned "Typically you would have a main form based on tblStatements and a subform based on jctLinkStatementDates", does this hint at a simpler way to do this?

[CJ_London]

If I drop a combobox on the form, how do I then add a field to pick a date that will be stored in jctLinkStatementDates together with DateTypeID of the selected date name/type?

the form (call it frmLinkStatementDates) would have a recordsource of jctLinkStatementDates, the combo controlsource be datetypeID, the rowsource of the combo would be something like

select datetypeID, datetype from tbldatatypes

the combo would have the following properties set:
bound column: 1
columncount: 2
column widths: 0 (hides the first column)

of the other two fields in jctLinkStatementDates, recordID will populate automatically if set to autonumber, and statementID will populate automatically if a) you have defined the relationship between your statements table and junction table and b) this form is a subform on a form with tblStatements as its recordsource. Neither of these fields need to be visible on the form or even have an associated control, just need to be in the recordsource.

And can I just set a preselected date name/type as a prompt (sort of like a label) if I want the date name/type to be fixed on a form and for the user to just select a date, instead of having to manually select the date name/type in a combobox?

set the combo default value to the datetypeID value of the type you want displayed

You've made a good start with your table structure, recommend you look at some of the many templates provided with access to see how these structures can work.

[shiraegi]

Thanks Ajax, much appreciated and I will take a look at the Access templates, thanks for the recommendation.

I was able to create a frmLinkStatementDates which dropped the LinkStatementDatesID (or recordID as you called), the DateTypeID, the StatementID and the DateValue labels and corresponding text boxes on it. I then created a combo and set it to pull the DateTypeID and DateType data from tblDateTypes and set the DateTypeID to show "Statement Date" as the default, locking it and disabling so it cannot be changed, and changing column widths to 0 to hide the DateTypeID column. The row source says:

SELECT [tblDateTypes].[DateTypeID], [tblDateTypes].[DateType] FROM tblDateTypes ORDER BY [DateType];

So now when I pick a date in DateValue, DateTypeID on the form shows the DateTypeID corresponding with "Statement Date", so that is great!

My question now is, how can I get another combo onto the form, using "Statement Received Date" as the selected DateType to be able to enter another date for that, and for both the Statement Date and the Statement Received Date to get stored in two individual records in jctLinkStatementDates? It seems like I can get one date to work with your kind input, but how about multiple dates on the same form that need to be stored in the same join table as individual records at once (with the different DateTypeID's, StatementID's and DateValue's stored)?

I will then try to get frmLinkStatementDates onto a frmStatements as a subform to get the StatementID to work...

[CJ_London]

My question now is, how can I get another combo onto the form, using "Statement Received Date" as the selected DateType to be able to enter another date for that, and for both the Statement Date and the Statement Received Date to get stored in two individual records in jctLinkStatementDates?

set the form to continuous

[shiraegi]

Okay, thanks for the pointer! I will check that out. Appreciate the help.

[shiraegi]

Actually, one thing I am wondering: I suppose it wouldn't be possible to do what I would like to do with a query instead of a form? I.e. have a query which shows the following fields:

From tblStatements:
- StatementID
- StatementName

From jctLinkStatementDates
- DateName (set to "Statement Date")
- DateValue for entry of the statement date
- DateName (set to "Statement Received Date")
- DateValue for entry of statement received date

With the other fields linking the tables with each other (including tblDateTypes) hidden. I guess that wouldn't work because the two DateName and DateValue fields should take up two records in jctLinkStatementDates, and the query would only show one record at a time?

[CJ_London]

don't understand what you are asking. If this is about data entry, you need to use a form

[shiraegi]

Sorry, I know it is a little tricky to explain. I was wondering if I could construct a query which shows the fields/columns StatementID, StatementName, a first default DataTypeID (Statement Date), DateValue relating to Statement Date, a second default DataTypeID (Statement Received Date), and a DateValue relating to Statement Received Date, and to then add or change data via that query, as I understand changes made in the query update to the tables. But I guess it wouldn't work because the two DateValues and associated DateTypes have to be two separate records (in jctLinkStatementDates) and I am not sure how that would work in one query record line...

I guess forms it is...

[CJ_London]

so it is data entry - use a form. Queries with multiple tables are not generally updateable. You might be able to do it to enter one date or the other, but not both

You could display the data using a crosstab query, statementID as the rowheader, datetype as the columnheader and datevalue for the value - but again not updateable

as an aside - users should never see tables or queries with view to input

[shiraegi]

Gotcha, thanks. Yes, at this time it would just be for me to update records once the whole table structure is complete, as the only user really for now (it should eventually serve to illustrate the functionality of a cloud app...). I will try to go with forms, it sounds like queries are not going to work well. The queries do seem to update though if the relationships between tables are set to cascade update? In any case, still lots to learn...

[CJ_London]

cascade updates is different - that applies only to related fields as PK/FK. Normally you would use an autonumber PK which cannot be changed so the cascade is irrelevant