Making a Dlookup answer fill in a table field..

[gangel]

Another question!!!





So I have my Table with Customers on it, one field is Date Collected. Another is Frequency of pickup (weekly, fortnightly, Monthly)

In my form I use

Next Pickup is

Source = [Last Collected]+DLookUp("Days","PackingFreqT","FreqID=" & [PackFreq])

Now this works!
Therefore if the customer has Fortnight as their Frequency, the calculation takes "previous collected date", adds 14 to it (by looking up 14 as the correct number for Fortnight) and then returns the new date for a next expected pickup..

HOWEVER this information is all in a form.. I want this to be in my table, and update my table value every time i change "last collected"

how do i make a form value go to my table if the control source already has a calculation in it?

Or am i doing this the COMPLETELY wrong way..?

Glenn

[June7]

Why do you need to save this calculated value? The 'next pickup date' is a dynamic value. It is a projected event. It should be calculated as needed. It doesn't need to be saved. What you save is the actual pickup date when it occurs.

Saving calculated values requires code - macro or VBA.

[gangel]

i want to save the calculated value onto the table so that i can use the table to create a report of all Pateints that have their NEXT pickup date in the next 3 days,

Therefore I need to take last pickup date + the value from their pickup frequency and then take that away from todays date.
Then i was going to make a query on that table with constraints of "show if pickup date is <3" or... at least that was my idea.

Therefore i can make reports and stuff for pharmacies that shows when to start packing the next patients packs.

I wouldnt be surprised if im doing this the wrong way though. sorry!

[ranman256]

Normally, the rule is: "you dont save calculated values....you calculate them."
This is what queries are for.
Your query will have a field that does the calculation..that way its ALWAYS live & fresh.

Use queries , not DLOOKUPS.

[June7]

As ranman says, do calc in query and use query for report.

If you save the value, risk data getting 'out of sync'.

[gangel]

Awesome! I did it
So i created a query with calcs in it and an IIF expression and got it working,
Then created a form based around that query!

SO i have gotten to the stage that i have customers listed that require packs to be made within the next 3 days.

What i want to do is add an "edit customer" button (as i have locked the cells on this form) which brings up my edit customer form.

My edit customer form relys on stating values "Forms!SearchPxF!FirstName" and "Forms!SearchPxF!FirstName"

So I want the above value to collect the data from [Firstname] and use it for "Forms!SearchPxF!FirstName" HOWEVER since i have a number of patients per page it doesnt know which [firstname] box to use the information from....

How can i specific which record the button points to since there are multiple records on the form?

[June7]

The code will use value from record that has focus.

Instead of searching on names, should probably search on customer ID.

[gangel]

k ill try that, so i could put something like...radio dots or something to help with "choosing focus"?
And ill put ID in front of names but not shown

[gangel]

still getting "the field [Firstname] could refer to more than one table listed in the FROm clause of your SQL statement

[June7]

Whatever cues user needs in order to understand record must be selected.

The query has multiple tables with field named [Firstname]? Need to include table name as qualifier.

[tablename].[Firstname]

Post the query SQL statement for analysis.

[gangel]

Hi again

I dont know how to find the SQL statement?

But also, All data is from the same table.

Table = CustomerInfoT
Query = CustomerPacksQ

I want to add a button for EACH LINE at the end of the line that says "edit Patient" and when you clik on it it opens "PatientInfoF" Form for THAT patient

[June7]

Open query in design view then switch to SQL view. That is the SQL statement.

Seems buttons don't work well for that. If I remember right, clicking the button doesn't actually put focus on the record (doesn't select the record). Better to use a textbox and its Click event.

[gangel]

ahhh ok thanks Seems a bit weird! So the text box would be part of that Record on that form?