form texbox for date and save/use for reports and query's (1 Viewer)

killerflappy · Nov 20, 2017

Hi guys,

Maybe a newbe question, but I can't find the right answer.

I have a form witch is used as an import module for different files to fill an orders-database.

In this form I want to set the delivery date, so all the rows of the import get this delivery value in a column. Also I want to use this value for some reports and query's.

Is it possible to use a textbox in a form and use this untill it's overwritten? I tried but I can't use the value of the textbox in the form and it isn't saved when closing and opening the form.

Ranman256 · Nov 20, 2017

the form would have a text box for the Date you want to use.
fill the date , then the import query would use this.

INSERT INTO tOrders ( Item2buy, Descr, Qty, Price,ImportDate)
SELECT Item2buy, Descr, Qty, Price, forms!myForm!txtDate
FROM tOrderBak;

the query adds the date in the textbox along with the data.
Use the BUILDER to get the path correct.

killerflappy · Nov 20, 2017

Hi Ranman256.

Thanks for help. This is step one.

No I want to be able to user the textbox in query's after the date is set and the form (with the date) is closed. During the test I did, the date wasn't saved. So how can I save this date? With the option to overwrite and save again.

Minty · Nov 20, 2017

Is this being run by other users ? The best way to achieve this is to store the value in a local table. Store the form name and control name, and value as fields then you can use a look up to set the default value(s) on form load.

On your form in the after update event store the value into you new table.

killerflappy · Nov 21, 2017

Hi Minty. One user sets this date. Then import the files. During import I can do some checks with the delivery date.

I feel a bit stupid. I made a textbox in the formheader and pointed it to =[tblDeliveryDate]![Delivery Date] . But nothing happend. No when I set the dat in the tabel and not when I choose a date in the formheader.

Minty · Nov 21, 2017

Use a Dlookup

Code:

= DLookup("[Delivery Date]","tblDeliveryDate")

You probably want to actually use this table for other system related local user things - and get rid of the spaces in your field names.

killerflappy · Nov 21, 2017

This is a good step Minty. I tried this before. I see the delivery date in the textboxon the formheader. also want to overwrite this int he formheader, so it is set in the table.

Get rid of the spaces is a good advise!

Minty · Nov 21, 2017

Use an update query on the after update event of the text box. Check it's a valid date before saving it.

killerflappy · Nov 21, 2017

Also did that. The problem is I can's change the value in the textbox.

I can do a workaround with a click-event, but it is better to just change it in the textbox.

Minty · Nov 21, 2017

Use the default value setting for the text box, don't set the dlookup as a control source.

killerflappy · Nov 22, 2017

Hi Minty,

This is it. With this I use a after update event to store the delivery date in the tabel. Tanks!

A last question:
I can refer to the textfield from the from to use the deliveryDate (= Forms!frmImport.DeliveryDatefrm). But I can't use this when this form is closed.

Also I can't simply use the stored date from tblDeliveryDate.DeliveryDate
When I refer to this, It shows a imputbox for tblDeliveryDate.DeliveryDate
Maybe because it hasn't got a relation.

What am I doing wrong?

form texbox for date and save/use for reports and query's (1 Viewer)

killerflappy

Registered User.

Ranman256

Well-known member

killerflappy

Registered User.

Minty

AWF VIP

killerflappy

Registered User.

Minty

AWF VIP

killerflappy

Registered User.

Minty

AWF VIP

killerflappy

Registered User.

Minty

AWF VIP

killerflappy

Registered User.

Similar threads

Users who are viewing this thread