link multiple Tick Boxes And Auto Date Input (1 Viewer)

stu_c · Aug 13, 2018

Hi all
I have several tables in brief are below are two issues I am struggling with.

1) What I am trying to do is when the Ready button is ticked on the TBLVehicleDefect form I want it to automatically Tick the VehicleAvaliable in TBLVehicleDetails

2) When Ready button is ticked I am trying to automatically add DATEOUT to todays date.

TBLVehicleDetails
>Registration
>Model
>VehicleAvaliable (Tick Box Yes / No)

TBLVehicleDefect
>Issue
>DateIn
>DateOut
>Ready (Tick Box Yes / No)

if you can give me some pointers be great!

Uncle Gizmo · Aug 13, 2018

The title of the thread and what you describe in the body don't relate to each other. Please could you give more information...

stu_c · Aug 13, 2018

I fail to see how the title does not describe what I am looking to do?

Uncle Gizmo said:
The title of the thread and what you describe in the body don't relate to each other. Please could you give more information...

isladogs · Aug 13, 2018

You need to add the Registration field to the second table and another field as the PK e.g. DefectID. Use Registration as the PK in the first table

1. You update the table not the form.
In the Ready_OnClick event, use a sql statement or update query to update the VehicleAvailable field to True

2. Do you mean update the Dateout field to today's date?
If so, add another Sql statement or update query and update the field to Date()
In both cases, make sure you filter for the selected vehicle only or all records will get updated.

jdraw · Aug 13, 2018

As Ridders has advised, you need to relate your tables based on your business rule(s).

It seems you have 1 or many Vehicles
A Vehicle can have 0, 1 or many Defects

Vehicle-->VehicleHasDefect<---Defect

??What exactly is the meaning of
- Ready
- VehicleAvailable

stu_c · Aug 13, 2018

Ready means ready to collect avaliable means avaliable to use just struggling to get the two boxes to link together

Uncle Gizmo · Aug 13, 2018

stu_c said:
I fail to see how the title does not describe what I am looking to do?

I did write out an answer for you, then before I pressed the post button, I read your question again, and it didn't make sense, to me that is... but looking at it again now, it does make sense, that's all I can say really!

jdraw · Aug 13, 2018

Stu,

Here is a link to info on Database Planning and Design that may be helpful.
Good luck

Mark_ · Aug 13, 2018

stu_c said:
Ready button is ticked

Are you referring to "OnClick"? The "On Click Event"?

Pat Hartman · Aug 13, 2018

Although Ridders told you how to solve your problem, no one mentioned that what you asked to do violates third normal form. Essentially, what you have constructed is a table where the value of a column is not dependent on the PK of the actual record but the value of some non-key field in a non-identifiable row of a different table so it is even worse than Codd ever have imagined.

Please do some reading on 1st, 2nd, and 3rd normal forms BEFORE continuing. You really need to restructure your schema before proceeding.

stu_c · Aug 16, 2018

Basically this is what I need to do
When any Repair box is not ticked in the SubForm then the Available box in the MainForm is unticked. (Picture Attached)

MainForm
IDVehicle (AutoNumber) (Relationship To IDVehicle)
Available (Yes/No)

Subform
IDDefect
IDFKVehicle (Relationship To IDVehicle)
Repaired (Yes/No)

Uncle Gizmo · Aug 16, 2018

Show the record source of the subform.

Sent from my SM-G925F using Tapatalk

Mark_ · Aug 16, 2018

stu_c said:
Basically this is what I need to do
When any Repair box is not ticked in the SubForm then the Available box in the MainForm is unticked. (Picture Attached)

MainForm
IDVehicle (AutoNumber) (Relationship To IDVehicle)
Available (Yes/No)

Subform
IDDefect
IDFKVehicle (Relationship To IDVehicle)
Repaired (Yes/No)

So this is really a calculated field used for display only then?

From a design standpoint, you may want to remove it as a "Yes/No" and change it to DtAvailable. You could then default it based off of repairs, but allow a user to change it to be later. If you go with that, add a text field to annotate why it won't be available when the last repair is completed.

This avoids issues when "Its done", but you won't have it as "Available" due to "Reasons". Often this will be something as trivial as "Waiting for Kay to clean the window" or such.

Pat Hartman · Aug 16, 2018

Forms!myform!myfield

The subform has many records. It doesn't make sense for the value from a random ONE of them to be carried on the parent record. The available box belongs ONLY on the main form. You might want to calculate the value on the main form whenever any subform record is updated. If all subform records have a complete date (better than a y/n field), then you can update the main form to be available.

But even this is dangerous since it violates second normal form and will be invalid if you have any update queries that modify the repair date.

MajP · Aug 16, 2018

You can use it in your main form query or in a calculated control

Code:

Public Function IsAvailable(IDVehicle As Long) As Boolean
   Const TableName = "tblDefect"
   If DCount("*", TableName, "IDFKVehicle = " & IDVehicle & " AND Repaired = False") = 0 Then IsAvailable = True
End Function

if used in a query for the main form

Code:

Select * , IsAvailable([IDVehicle]) as Available from YourTable

MajP · Aug 16, 2018

And as mentioned get rid of the field Available. You just need to calculate it when it is needed. It can also be done as a subquery without any code.

stu_c · Aug 17, 2018

MajP
thank you for the below code, I have tried it and for some reason the Repaired box now stays stuck on when I tick it and nothing happens with the Available box :\ im really stuck on what to do

MajP said:
You can use it in your main form query or in a calculated control

Code:

Public Function IsAvailable(IDVehicle As Long) As Boolean Const TableName = "tblDefect" If DCount("*", TableName, "IDFKVehicle = " & IDVehicle & " AND Repaired = False") = 0 Then IsAvailable = True End Function

if used in a query for the main form

Code:

Select * , IsAvailable([IDVehicle]) as Available from YourTable

MajP · Aug 17, 2018

I am not sure about the stuck issue, that should not be related. The problem I was not thinking is that since it is built on a query you would have to do a requery after the update of the repair button change. If not it will just show you whatever the available status is when you first navigate in the main form

There are a few ways to do what you want. I will upload a demo mock-up.

MajP · Aug 17, 2018

In order to refresh the main form you add this to the subform of the repair checkbox.

Code:

Private Sub repaired_AfterUpdate()
  Me.Parent.Refresh
End Sub

I did two demos. The first uses a calculated control in the checkbox and no calculation in the query.

Code:

=IsAvailable([IDVehicle])

The other uses the calculation in the query.

link multiple Tick Boxes And Auto Date Input (1 Viewer)

Registered User.

Nifty Access Guy

Registered User.

MVP / VIP

Super Moderator

Registered User.

Nifty Access Guy

Super Moderator

Longboard on the internet

Super Moderator

Registered User.

Attachments

Nifty Access Guy

Longboard on the internet

Super Moderator

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Attachments

Similar threads

Users who are viewing this thread