Form with a Date

[lenlab]

MY issue is I have a form where I have data to be entered as well as a drop down list box with all the Sunday dates of the current year. Weekly for each record created the User will enter information on the form and select a Sunday Date from the drop list down box. This is always the past Sunday's date. So the User will repeat the step of going to the list box scrolling though it and selecting the same date in the box for many records. This is not very efficient. The date to be selected only changes when data is entered the next week.



I would like to have the User just select the date once and not have to select it again for the next and ensuing new records on the form. There could be dozens of records or more.

I thought the Solution might be to have a separate form and query where the User selects and sets the Current Sunday date. If I do that how do I get that date into the form so it will be used and updates the forms underlying table.

Or is there another and better way? I'm kind of self taught with Access so probably went about it the wrong way.

I hope this made sense when I explained my predicament.

[pbaldy]

You can set the default value of the textbox to the value of the form where the user selects the date, or:

http://access.mvps.org/access/forms/frm0012.htm

[lenlab]

Thanks!

I could not use the suggestion in the link as it is way over my level of Access knowledge. I would need someone to hold my hand and explain as I did it.

However I did work with the setting the textbox default value suggestion and that seemed to work with what I did up to this point. What I don't like is having to open a form A and keep it open so it has a date value for form B.

I tried accessing the Date as the default from the table that form A updates but I guess it isn't allowed.

[pbaldy]

You can hide form A if you want. Or you can have them select it in the header of form B.

[lenlab]

Thanks. But no. I'm not trying to hide form A. The User has to select the current Sunday date from form A for use in form B.

The way it will work is the User would open form A and from the drop down of Sunday dates select the current Sunday Date. The user would then have to keep form A open and navigate to form B where it picks up the Sunday date from form A and for each new record. It works fine I just did not like the User having to keep form A open and navigating. Usually when the application is done with a form it is closed and we move on to another form to enter data.

Seems odd to me that Access would take data from a form in this case and use it within another form but I could not get to the table and use the date that Form A puts in it. I thought I was missing something. After trying all kinds of stuff I came to the conclusion that form B can only get the date from form A.

Thanks again for your continued help. It must be hard for you to figure out what I am doing w/o actually seeing the application.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post. Identify forms involved in issue.

[lenlab]

I think a sample of the database is attached. It is BEFORE I attempted to make changes so the date did not have to be re-input for every new record. I used 7zip to compact it.

Use the Main macro to open up. Go to Sunday Collection button to see the various categories for inputting money from the collection. They all use a drop down list for the Sunday dates.

If you want a sample of the database after I made some changes (new date table, delete query, new date form) let me know.

[June7]

That is just a Word document.

[lenlab]

I think I now have the correct file attached. Sorry about that.

When I first zipped the database for you, 7zip formatted in a .7z format that would not load. I did unzip it and it was the database so I went back in and redid it to .zip and in my haste grabbed the wrong file. I did not check that one and I should have.

Thanks again!

[June7]

Database design looks like a mess to me.

Multiple similar tables (Jan Monthly Expense, Jul Monthly Expense, Jun Monthly Expense, etc) should be one table with another field for, in this case, the month attribute.

Other Giving Identified and New Giving tables differ in only 1 field. I would have 1 table.

This design is carried through to queries, forms, reports. Multiple identical objects when there should be 1 and apply filter as needed. Personally, I don't put dynamic filter parameters in queries. I apply filters to forms and reports that use queries as RecordSource.

I opened Main Menu, selected Sunday Plate Collection, then Sunday Collection, which opens New Giving form. I see listboxes to select Env and Date and enter Amount. That works.

If you want the form to open with the Date box filled in with appropriate value, that can be done, then if you want that value to carry forward to each new record entered on that open form, that can also be done. Paul gave example of the latter in post 2.

Other issues I see:
Spaces in names of objects and fields. Date used as field name - date is a reserved word. Should avoid both in naming conventions.

[lenlab]

Thanks for the critique. I will look into fixing what I can from your comments. Looks like I have a lot of fixing to do. This database started out as an experiment and just kind of grew into a monster. I readily admit that I was feeling my way and probably did a lot of bad things. However it does work quite well.

Now would it be possible for you or Paul to make the change suggested to the date issue to the DB I sent you and send it back to me for me to see what was done and where it was done? Then I could apply it to the production version after I test it?

[June7]

I don't use macros, only VBA. So try:

Private Sub Form_Load()
Me.Text10 = Me.List12.ItemData(0)
Me.Text10.DefaultValue = "#" & Me.Text10 & "#"
End Sub

Private Sub Text10_AfterUpdate()
Me.Text10.DefaultValue = "#" & Me.Text10 & "#"
End Sub

[lenlab]

I tried it and received error. Am I supposed to use this code as is or do I need to put some information in it? A brief description of the error follows:

Ambiguous Name detected: Form_Load

[June7]

That means somehow the Form_Load event is in the form's code module twice. Access is confused. Combine code from the two procedures or just delete the previously existing procedure. Regardless, there can be only one.

[lenlab]

OK....I see that I really should not have taken all the code just a piece of it. I made changes since my last post and your response and it now seems to work for the most part (it seems to keep the date) but I do get this error:

Run Time error: '-2147352567 (80020009)
The value you entered is not valid for this field.

When I go to the debugger it highlights this entry: Me.Text10 = Me.List12.ItemData(0)

It looks like I am real close to solving this with your expertise telling me what to do.

Oh one other bit of info, when I get this error if I just click on the End tab and I can get into the form just fine and the date issue is resolved. However this error would be nice to not have to deal with.