How to set a table field to date by year?

[Collins]

Tried duplicating this in another test database and I'm receiving the following error... "You tried to assign the Null value to a variable that is not a Variant data type." The only thing that is different is that it's in a sub-form. Also... I'd really like to give the user the idea he or she is in control of when the date carries over by having them push a button. So if I could get some suggestions on how to do that I would greatly appreciate it.

[June7]

Why a button? The user has control by entering different value. So you want the DefaultValue to be empty unless user clicks a button? If this is what you want then put the code in the button Click event.

Post your attempted code.

[Collins]

I saw a database onetime that had a little oval button next to the fields that could be repeated from record to record. Like a city, a state or a zip code. If it was off the color of the button was same as the background with a black outline and it looked raised. If the user wanted the information to carry over they would click the button which would look depressed and would turn red. Then from record to record the user knew what information was being carried over to the next record by looking at what red buttons they had on the form. I think that is a neat feature and would like to include it to some fields in my traffic database. At this particular time the year the crashes occurred. But I'm receiving the error message and the code isn't working.

[June7]

Okay, the buttons are an eye-catcher but not sure there is any real gain in performance or 'user friendly'-ness.

Need something to analyze.

[Collins]

Let me know if this works... And mind you this is simple test database to test all my ideas until they work and then I'll eventually incorporate all of them into a real database.
Working Test Database2.zipWorking Test Database2.zip

[June7]

Don't understand Relationship. You have it set up so that IntersectionsBrokenOut can have only one Crashes record associated with each intersection.

Why are there two Intersections tables?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Collins]

Don't understand Relationship. You have it set up so that IntersectionsBrokenOut can have only one Crashes record associated with each intersection.

Why are there two Intersections tables?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Then I have the relationship set up wrong. It needs to be one year "2012" for example with 2 crashes that year and then another year "2013" with no crashes that year and so on. So many years of crashes for each intersection. So how do I switch the relationship? I thought you just dragged one over to the other and it happened.

One table has the intersections together. The second "Broken Out" table has the intersections broken out into 2 individual fields instead of one.

Great fine I'll take the dashes out of my naming convention and use underscores instead.

Are you getting the same error message when you enter a date in the subform on Frm-Main?

[June7]

The only difference in tables is the Hwy field. So why keep AllTogether?

Didn't try any data entry after viewing the Relationship.

Intersections table has field for CrashID. This means an intersection can have only one associated crash record. Is this what you want? Can't an intersection have many crashes in many years?

If this relationship is correct, your form/subform is backwards.

[Collins]

The only difference in tables is the Hwy field. So why keep AllTogether?

Didn't try any data entry after viewing the Relationship.

Intersections table has field for CrashID. This means an intersection can have only one associated crash record. Is this what you want? Can't an intersection have many crashes in many years?

If this relationship is correct, your form/subform is backwards.

No I think my relationship is backwards. I want one intersection to have many crashes in many years. So I named the field wrong in the intersections table? What should I have called it. My class told me to name them the same every time...

I'm keeping all together in case I change my mind and no longer wish to use Broken Out. I don't want to have to recreate that table all over again.

[June7]

What is the relationship?

Crashes table appears to be summary in nature (NumberOfCrashes field). Consider:

tblIntersections
IntersectionID (PK)

tblCrashes
CrashID (PK)
IntersectionID (FK)
CrashDate
CrashType

This allows intersections to have multiple crashes associated and each record in tblCrashes is a single crash event, not a year summary.

Then your form/subform arrangement is correct except for the Master/Child Links should be IntersectionID.

[Collins]

What is (PK) and (FK)?

[Collins]

Ah!!! (PK) Primary Key... (FK) "notsure" Key...

That seemed to fix it. Thanks a ton...

[June7]

FK means Foreign Key.

An alternative data entry form is Frm-Crashes as a standalone with a combobox to select intersection.

[Collins]

FK means Foreign Key.

An alternative data entry form is Frm-Crashes as a standalone with a combobox to select intersection.

Not a bad alternative. I'll have to experiment with that a little.

One thing still remains. On my button... I know you hate it but... If I put the code in the click event it works. But how do I turn it off when I click the button again?

[June7]

If Me.control.DefaultValue = "" Then
Me.control.DefaultValue = Me.control
Else
Me.control.DefaultValue = ""
End If

Maybe use a Toggle button instead of Command button.

If Me.toggle Then
Me.control.DefaultValue = Me.control
Else
Me.control.DefaultValue = ""
End If