Populate form from date control

[HectorGips]

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]

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]

Spend some time here and you will be a query wizard

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Very good and easy to understand.

 

[MajP]

There are some better custom calendar controls on the internet. Here is one

A better date picker

Some months ago I uploaded a replacement date picker (see DatePicker.accdb attached) that has no Active X controls and can also be used in 64-bit Access. It was originally posted in response to a question about Stephen Lebans' calendar in this thread...

www.access-programmers.co.uk

from @isladogs. They are easy to use and have some advantages.

 

[HectorGips]

Spend some time here and you will be a query wizard

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

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]

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]

There are some better custom calendar controls on the internet. Here is one

A better date picker

Some months ago I uploaded a replacement date picker (see DatePicker.accdb attached) that has no Active X controls and can also be used in 64-bit Access. It was originally posted in response to a question about Stephen Lebans' calendar in this thread...

www.access-programmers.co.uk

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]

See demo

 

[HectorGips]

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:


And another for the production report:

 

[HectorGips]

See demo

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]

If you try to move to a non existent date nothing will happen. Add an unbound combo for the shifts.

 

[MajP]

See update

 

[HectorGips]

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]

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]

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]

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]

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]

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!