Populate form from date control (1 Viewer)

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
Hi there, guys.

I have a form with several fields from a table. One of them is the date. I'd like to populate this form by clicking in the date control and picking a date from the calendar picker. If the record exists, the form is filled with the data and the operator is able to update the record, otherwise and advise pops up.

I'm struggling with queries, queries of queries, etc. but I'm lost right now, by separate I get things working but not when I put them together. I'm afraid that I'm missing some concepts due to my lack of experience in Access.

Which is the best approach to this issue? I don't need, at least yet, the code itself, but the best way or method to do this.

Thanks in advance, and regards.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
If it is a date field you automatically get a date picker next to the control depending on your version of access (I believe 2007 and afterwards)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
Spend some time here and you will be a query wizard
Very good and easy to understand.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
There are some better custom calendar controls on the internet. Here is one
from @isladogs. They are easy to use and have some advantages.
 

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
Spend some time here and you will be a query wizard
Very good and easy to understand.
Magnificent link, MajP. Thank you very much. It's the kind of tutorials I really enjoy learning from, as you say easy to understand.

If it is a date field you automatically get a date picker next to the control depending on your version of access (I believe 2007 and afterwards)
About that, I do have already a date picker, but I don't know how to proceed from here.
I mean, in that form, if I pick a date, it's inserted in the text field. But what I want is the following:
1. The user pick a date in the date picker.
2. The record corresponding the date picked populates the form text fields. Some of the fields are empty yet.
3. The user fills the empty fields with data that he has recovered and updates the record with that remaining data.

I know how to do all the three steps by separate, but not together. I can't go from 1 to 2, in fact I don't know if it's possible to do.
The main reason for this "working design" is that I want the db the easier and more user friendly possible.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
The record corresponding the date picked populates the form text fields. Some of the fields are empty yet.
Not sure if I understand. That sounds to me like you have records in the database. You want to pick a date and search for an existing record on that date.
If that is correct the date field needs to be unbound. I would probably use the custom date picker linked because you could always get to it easily.
Can you post your database of at least a screen shot of the form?
 

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
There are some better custom calendar controls on the internet. Here is one
from @isladogs. They are easy to use and have some advantages.
I was watching this, but I'm from Spain, and here the week begins on monday. I couldn't manage to change that. Not critical, but enough to try another way. I guess I'll give it a second look.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
See demo
 

Attachments

  • SearchByCC.zip
    166.7 KB · Views: 110

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
Not sure if I understand. That sounds to me like you have records in the database. You want to pick a date and search for an existing record on that date.
If that is correct the date field needs to be unbound. I would probably use the custom date picker linked because you could always get to it easily.
Can you post your database of at least a screen shot of the form?

Sure. The process is this:
- The factory manager creates a production order for each day of a week. This creates a new record in the db for each day. It has empty fields so some production data has to be obtained from the production process.
- The operator, once every production workday has finished, fills the gaps in the database with the results of the production (tones, hours needed, supplies used, etc.). And he does this by picking a date in a production report form.

I have one form for the production order:
1582009864305.png

And another for the production report:
1582009864305.png
 

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
Works perfecto, man. But I don't manage to understand how it works without a query. Just by referencing in the form that the data origin is the table? No macros, no queries?

I need it to advise if a day record is not present, like a day off or a sunday when a production order wasn't made.
And would be perfect if the user could pick not only the day but also the shift, because sometimes we have two or three shifts a day when the orders get increased. So the db would have two or three records for a single day.

EDIT: I didn't notice the macro in the search button. Sorry, my bad.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
If you try to move to a non existent date nothing will happen. Add an unbound combo for the shifts.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
See update
 

Attachments

  • SearchByCC2.zip
    155.1 KB · Views: 105

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
See update
Amazing, MajP. That's just what I need, and so easy to implement.

I'm going to adapt your solution to my db, and I'll let you know about the results as soon as posible so we can set the thread as solved.

Many thanks!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
Good luck. There are a lot of Access smart helpful people here that can save you a lot of time. Do not be afraid to ask questions, everyone here is looking to help. I am not smart but am Access smart.
 

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
Hi there!
It's giving me trouble with the data type after I adapted it to my db.
For the shifts, we use letters instead of numbers (M for morning, T for evening, N for night).
I made a combo with this letters, and when I run a search it gives me the error 3070, so it's something related to the data type. I guess that it's expecting a number.
I can't see where change the data type to make it works.
By the way, if I just select a date and leave the shift combo empty, the macro runs ok, as expected.
From the code I just adapted the field names, nothing else.

Thanks!
 

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
Well, I managed to solve it somehow... I have changed this:

strFind = "IdDate = #" & Format(Me.txtIdDate, "MM/DD/YYYY") & "# AND Turno = " & Me.cmboTurno

to this:

strFind = "IdDate = #" & Format(Me.txtIdDate, "MM/DD/YYYY") & "# AND Turno = " & Chr(34) & Me.cmboTurno & Chr(34)

The thing is that cmboTurno is text, so it needs to be quoted in a special manner. I found out there that Chr(34) equals a quotation, but I'm not sure in what way it does. Equals to ""? Or to """"? It's confussing to me.

So it's solved, but if someone could explain me the equivalence for Chr(34), would be really helpful to me.

Thanks and regards.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
chr(34) is '
you need single quotes around text in sql. 'SomeText' or #somedate#, but numerics need nothing around them
strFind = "IdDate = #" & Format(Me.txtIdDate, "MM/DD/YYYY") & "# AND Turno = '" & Me.cmboTurno & "'"
 

HectorGips

New member
Local time
Today, 13:37
Joined
Feb 13, 2020
Messages
20
chr(34) is '
you need single quotes around text in sql. 'SomeText' or #somedate#, but numerics need nothing around them
strFind = "IdDate = #" & Format(Me.txtIdDate, "MM/DD/YYYY") & "# AND Turno = '" & Me.cmboTurno & "'"

Crystal clear now... So easy again...
My next step has to be to learn a little bit (or quite a bit) about this character and "grammar" stuff. Otherwise I'll waste too much time in things like this, a missing quote, a colon or semicolon or whatever.

Many thanks for the explanation!
 

Users who are viewing this thread

Top Bottom