How to create a search form

[braimi]

Update a field from another table field only for new records

I am stuck with updating a field record with a field from another table.

I have two different forms.
Form 1:
DateId(AutoNumber & PrimaryKey)
SelectDate (Date/Time, Format "Short Date")
DateChanged (Date/Time, DefaultVaule= "Date()")

Form 2:
RemitDate (Date/Time, Format "Short Date")
RemitTime (Date/Time, DefaultValue="Time()")

I actually want that when user enters a new date in form 1 and update it then RemitDate on form 2 becomes equal to the latest updated date in form 1 only for the new records in form 2.

Previous record dates on form 2 should not be disturbed.

Please help me to get this problem solved.

Thanks

 

[chergh]

Take a look at: http://www.access-programmers.co.uk/forums/showthread.php?t=99353

 

[Ashraf]

Hi
The follwing file will help you!
download it from the following link
http://rapidshare.com/files/388524552/search2000.mdb.html

 

[braimi]

Hello
Thanks to both of you Ashraf and Chergh. I have solved the search form issue but I am having another question that is

I am stuck with updating a field record with a field from another table.

I have two different forms.
Form 1:
DateId(AutoNumber & PrimaryKey)
SelectDate (Date/Time, Format "Short Date")
DateChanged (Date/Time, DefaultVaule= "Date()")

Form 2:
RemitDate (Date/Time, Format "Short Date")
RemitTime (Date/Time, DefaultValue="Time()")

I actually want that when user enters a new date in form 1 and update it then RemitDate on form 2 becomes equal to the latest updated date in form 1 only for the new records in form 2.

Previous record dates on form 2 should not be disturbed.

Please help me to get this problem solved.

Thanks

 

[PNGBill]

What you may be saying is that you want Form Two to get it's Remit Date from the date of the last record in Form 1.

Not sure how to do this but when you query records you can get the last record - look for Top in access help.

Maybe a query that returns the last record will be the DLookup source for Remitdate in form2.

This sql returns just the last date in your Remittance Table

SELECT Last(RemittanceDetails.RemittanceDate) AS LastOfRemittanceDate
FROM RemittanceDetails
ORDER BY Last(RemittanceDetails.RemittanceDate);

Your DLookup in form2 could be DLookup("[LastOfRemittanceDate]", "QryRemittanceRecordLast") see if this works.

 

[braimi]

Thanks a lot Bill.... It really worked great.....

Now another question that how can I use the following where clause in query

Where RemittanceDetails.RemittanceDate=[forms]![frmDayEnd]![SelectDate] - 1

I actually want here that when user selects a date on DayEnd form and when he clicks generate report so the report should come up with the previous date to what is selected on the form.

Also I want that user should not be able to select previous date.

 

[PNGBill]

Check access help (F1) and look for date. You can use a few options eg Today() is today's date Today(-1) is yesterday - check if I am correct but this is the type of functions that exist.

The help has some good examples on using date in expressions which from memory include reporting on events that happened so many days proir to today or a selected date.

Here is my access help for "date Expression and then checking through some of the results I ended up with:

Examples of manipulating and calculating dates in calculated fields@import url(/Office.css);Examples of manipulating and calculating dates in calculated fields

You enter the expression in the Field cell in the query design grid. The fields you reference in the expression are from the tables or queries in the current query.
Expression Description LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Uses the DateDiff function to display in the LagTime field the number of days between the order date and ship date. YearHired: DatePart("yyyy",[HireDate]) Uses the DatePart function to display in the YearHired field the year each employee was hired. Date( )- 30 Uses the Date function to display the date 30 days prior to the current date.


You could try Date([fieldname])- 1 to get the date 1 day prior to the date in the field you specify.

 

[braimi]

Thanks Bill.... I have resolved the issue...
To restrict the user to today or next date is just simple that in Validation Rule of the field I just entered =>Date() and that solved this problem

and with where clause also the statement was easy
WHERE RemittanceDetails.RemittanceDate=DateAdd("d",-1,[forms]![frmDayEnd]![SelectDate])
and it worked superb.

Thanks for your great help. I have almost finished my project and will disturb you again if I will be stuck some place...

Regards
RizKhan

 

[PNGBill]

You seem to be learning fast.

 

[braimi]

Thanks its all because of your help because the first problem where I got stuck with printing of current records was really making me to get rid of learning access. but you helped me and I was able to learn some more when you showed me the solution for that.

Thanks once again.