comboBox mulitiple fields

AccessBlaster said:
Dick:
Is this what your look for?
attachment.php
Click to expand...

Thank you a whole bunch,
What is the advantage to using this method verses using autoexec?
 
You're welcome.

I don't like macros. I'm not sure at what point this feature became available but I have no databases that use an AutoExec macro so I'm sure it's been available for 20 years or more.

I also don't like tabbed documents, which is the default. This is set with the property settings on this same page and if you look down a little more, you'll notice that the AutoCorrupt option is turned off.

If you don't like the colors I picked, change the theme.

On the reports form, when you enter the from date, code populated the through date at from +7 but you can override the calculated date to do a longer period if you want to.
 
Last edited:
Deliberate. This is a dangerous feature. I know how it works and yet it still gets me if I accidentally leave it on. I've attached two files. The .doc file is the original MS documentation with a URL to the original which may no longer be valid. The PP was created by me for a presentation I gave to my local Access user's group. Their eyes glazed over and I lost half of them, the subject is so complex. Try to follow item 4 in the PP. It is the most straight-forward.
 

Attachments

Pat Hartman said:
Here it is again. This time I changed the name to avoid confusion.
Click to expand...

Hi Pat,
I have downloaded this to take a peek?
When I open it I get 'Class not registered' on line

Set con = Application.CurrentProject.Connection

in FillOptions()

Would that be because I only have 2007?
 
Check your references and see if one is marked missing. Nothing in the app needs the latest version of Access so just choose the proper library for your version. In fact, if you convert the app to an .mdb, it should still work.
 

Attachments

  • References.JPG
    References.JPG
    44.9 KB · Views: 81
Pat Hartman said:
Check your references and see if one is marked missing. Nothing in the app needs the latest version of Access so just choose the proper library for your version. In fact, if you convert the app to an .mdb, it should still work.
Click to expand...

Will do. It happened on my computer in work.
I have just downloaded it to my home computer and it opens fine.:)

Thank you.
 
Pat Hartman said:
Deliberate. This is a dangerous feature. I know how it works and yet it still gets me if I accidentally leave it on.
Click to expand...

Yes I thought it was deliberate ....
I'm very aware of the dangers of Auto Correct though I found your PowerPoint presentation a very clear summary of the issues.
The only one that was new to me was the form/subform issue - point 7 on slide #18.

I thought this was very good advice to prevent issues
My suggestion – leave Name AutoCorrect turned off. Then when you need to make changes to the names of objects and you want to take advantage of the help Name AutoCorrect offers, turn it on. Make the changes, open all affected objects, turn it off.
Click to expand...

Having said all that, I understand the auto correct feature was substantially improved in Access 2003 or thereabouts.
For many years, I always switched it off because of the risks but recently have reverted to leaving it switched on again.

However, I'm careful to check the effects of renaming items and so its not caused me grief leaving it on .... yet!
 
Last edited:
Pat,
I need to add the time of each meal. First as you suggested make sure I understand what is happening.
1. The items on the sfrmMealLogDetail gets data from qMealDetailEntery
2. qMealDetialEntery gets data from tblFood which is co-joined by the main ID in tblFood (FoodID and the child ID (foodID) in tblMealLogDetail)
To add time eaten I would have to add a field timeEaten to the table (tblMealLog) with format (Medium Time)
Then add (timeEaten) to qMealDetailEntery
Then add (timeEaten) to sfrmMealLogDetail
Did I miss anything
 
There is a field named WhenEaten. It is in tblMealLog. it contains both date and time. Separating date and time into two fields only adds complexity so I don't do it, especially when creating something for myself. Creating something for someone else, I might have done something different.

If you have to have them separate, then you have to add a new field to tblMealLog and a new control to frmMealLog and change the format of WhenEaten to show only short date. The time control would have a format of short time. qMealDetailEntry does not include tblMealLog so it does not need to be modified.

PS - adding formats to the table simply hides the actual table contents and will eventually lead to confusion so I never format table columns. I only use formats on controls and only if necessary. So in this case, you want to restrict WhenEaten to a date and TimeEaten to time so you would use short date as the format - NEVER a specific date format. That leads to other problems especially if your date format is not US standard m/d/y
 
Pat Hartman said:
There is a field named WhenEaten. It is in tblMealLog. it contains both date and time. Separating date and time into two fields only adds complexity so I don't do it, especially when creating something for myself. Creating something for someone else, I might have done something different.

If you have to have them separate, then you have to add a new field to tblMealLog and a new control to frmMealLog and change the format of WhenEaten to show only short date. The time control would have a format of short time. qMealDetailEntry does not include tblMealLog so it does not need to be modified.

PS - adding formats to the table simply hides the actual table contents and will eventually lead to confusion so I never format table columns. I only use formats on controls and only if necessary. So in this case, you want to restrict WhenEaten to a date and TimeEaten to time so you would use short date as the format - NEVER a specific date format. That leads to other problems especially if your date format is not US standard m/d/y
Click to expand...
Thanks, I have so much to learn, but loving it.
I have to tract the time of day that I eat ever meal.
When you say you only format the control, does that mean not formatting the field (date) in the table?
 
I have to tract the time of day that I eat ever meal.
Click to expand...
I know that. Look at the WhenEaten field. You will see that it includes the date AND the time.

NEVER format fields on tables when the formatting will mask the actual contents. Formatting a date on a table field to short date will NOT prevent time from being recorded. It will only prevent you from SEEING IT. You WILL see that I have applied formatting to the fields defined as currency. They are currency data types to avoid floating point errors (see good article on fmsinc.com "when access math doesn't add up") but I don't want them to be formatted as currency. In your case, i want them to be formatted as standard with 1 decimal place. In other situations, I might want them to be formatted as percents. This doesn't hide data (although it might hide decimal digits if these fields were calculated but they are not. If they were calculated, I would use standard but leave the decimal digits at auto or 4 which is the max so nothing would get hidden.

Me.MyDate = Now() will store the current date AND time REGARDLESS of how you format the field on the table. Dates are not stored as strings. They are stored as double precision numbers. The integer is the number of days since Dec 30,1899 and the decimal is the fraction of the day since midnight. If you open the table in DS view, you will not see the time even though it is there and you will waste hours/days even trying to figure out why your criteria is not working correctly on some records.

If you need formatting, do it on the controls on forms or reports.
 
Pat,
When I try and add a new meal to the Meal Log choosing (meal type- Breakfast), (person-Pat Hartman), (when eaten-new date [12/30/2017]), and then try a food I get this message
Food Log
{The changes you requested to the table were not successful because they would create duplicate values in the index. Primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.}

What am I doing wrong?
 
Sorry, I left out a field when I made the unique index.

Open tblMealLog in design view and if the indexs are not showing, press the indexes icon on the Design tab. The UniqueIDX needs a third field. Add WhenEaten as shown in the picture. Save and close.
 

Attachments

  • UniqueIDx.JPG
    UniqueIDx.JPG
    67.8 KB · Views: 79
Pat Hartman said:
Sorry, I left out a field when I made the unique index.

Open tblMealLog in design view and if the indexs are not showing, press the indexes icon on the Design tab. The UniqueIDX needs a third field. Add WhenEaten as shown in the picture. Save and close.
Click to expand...

Takes food ok, but no time just date.
 
You changed the date and time fields. What did you do?
 
Pat Hartman said:
You changed the date and time fields. What did you do?
Click to expand...

I had changed the date field on a copy that I was converting, but now I am working on a virgin copy that you sent. (see morning snack Pat Hartman 12/20/207)The only thing I changed was the index that you sent yesterday.
 

Attachments

You can't add the same meal type multiple times on the same day. I made 7 different meals so unless you need to know exactly what time you ate something, the meal type should place the eating at an approximate time of day. it also helps you if you want to analyze at what time of day ("meal") you are consuming the most calories.

If you want more than 7, just add them to the tblMealTypes.

If you want to allow multiple entries of the same meal type on the same day, you can do that by removing the unique index but I don't recommend it.

Merry Christmas
 
You must log in or register to reply here.

Similar threads

A
Solved Form Combo Box Doesn’t Store Selected Value (Foreign Key Issue)
2
Replies
20
Views
497
GPGeorge
GPGeorge
E
Need Help with Combo Box
Replies
4
Views
483
Pat Hartman
P
S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
366
SkeletorPlus
S
A
Problems with editing and calling multi-Select (Multiple Value) fields
Replies
5
Views
550
MadPiet
M
A
Having trouble with a combo box
Replies
17
Views
869
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom