Full Version: Inserting date
UtterAccess Forums > Microsoft® Access > Access Forms
Tim
Hi,
I have attached a sample db. There is a combo box on the form. After update it will produce a report.
On that form there is another text field with the default value of Now().
I was wondering how I can insert that Now value to only to the delegate records which are on the list...
Basically, I am going to send forms to all the delegate...and I need to update the form send date on the delegate table..
Is there a way to do that??
Please can someone kind enough to look on to the databaase attached and let me know possible solution??
Cheers
Tim
Again, Forgot to attach. tongue.gif
iandouglas
An update query is my first thought based on the query that produced the report & updating a field with Now() or Date() if you only need the date & not the time.
Tim
The query producing the report is quite complex......I am not sure about putting Update on that..
I will have a go..
Tim
Hi,
can update the date with no problem... However...I have got the follow up question...that is..is there a way, i could hid you are about to update (number of) records..are you sure..blar blar blar..
Instead , is there a way, I could show a custom message with YES or NO..Whilst YES will update the records, NO will cancel the update operation...but if there is 0 rows to update...another message should come up first saying a custom message...
Currently I am working with the following query..without the proper custom messages...Can someone help me on this?
UPDATE tblDelegates SET tblDelegates.CerSendDate = Now()
WHERE tblDelegates.pkDelegateID IN
(SELECT [tblBookings].[fkDelegateID]
FROM tblSchools INNER JOIN ((tblCourses INNER JOIN tblEvents ON [tblCourses].[pkCourseID]=[tblEvents].[fkCourseID]) INNER JOIN (tblDelegates INNER JOIN tblBookings ON [tblDelegates].[pkDelegateID]=[tblBookings].[fkDelegateID]) ON [tblEvents].[pkEventID]=[tblBookings].[fkEventID]) ON [tblSchools].[pkSchoolID]=[tblDelegates].[fkSchoolID]
WHERE ((([tblCourses].[pkCourseID]) In (105,114,117)))
GROUP BY [tblBookings].[fkDelegateID], [tblSchools].[fkLocationID], [tblDelegates].[CerSendDate]
HAVING (((tblSchools.fkLocationID)=[Forms]![frmFormsSending]![Combo6]) AND ((Count(*))=3) AND ((tblDelegates.CerSendDate) Is Null)));
---
iandouglas
Hiding the message is easy. Under tools:Options:Edit/Find there's an option to Confirm action queries. Uncheck that & you won't be asked again.
Is to custom messages I suggest you use consider a popup form with a message & yes/no command buttons yes runs the update query & no aborts. You could use a select query to work out if there are records to update & generate an appropriate message.
Tim
I changed my mind. No pop up messages. But on click on the report, I will update the records.
On the On No Data event of the report, I put the custom message.
owever! there is one more problem. When there is no data, the custom message will come up and tells me there is nothing to print blar blar blar..and when I click on OK..the error message will come up and point to the After Update Event which is trying to open that particular report...
How can I solve that problem?
By the way, are you City fun or Utd fun?
Cheers
Tim
Larry Larsen
Hi Tim
Some thing like this:
CODE
Dim strMsg As String
strMsg = "Your about to update your records." + vbCrLf
strMsg = strMsg & "Do you wish to Update the records?" + vbCrLf
strMsg = strMsg & "Click Yes to Update or No to Skip Update..."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Update Records?") <> vbYes Then
'...Do some other action if "No"....
Else
'....Run your record Update code/routine/query..
Dim QryName As String
QryName = "[color="red"]YourUpdateQueryName[/color]"
CurrentDb.Execute QryName, dbFailOnError
End If

would prefer not to switch "off" error messaging but to control it..
thumbup.gif
iandouglas
I think Larry & I are overlapping responses. In my report On No Data event I have something like
Private Sub Report_NoData(Cancel As Integer)
DoCmd.OpenForm ("frmNoData")
Pause (2)
DoCmd.Close
Cancel = True

End Sub
The popup form opened has a simple 'There's no Data' message & the subroutine Pause waits for 2 secs then closes the form & Cancel=True aborts the report.
Neither, the all to brief no football off season is a joy to me. Bring back international tiddly winks!
Larry Larsen
Hi Ian
We'll talk amongst ourselves and let Tim settle and ponder.. on the next idea..lol

thumbup.gif
iandouglas
Hi Larry
ets do that! Happy New Year!
Ian
Tim
Hi,
little bit of problem. The report I am trying to open is based on a query. I am opening the report from after update of a combo box.
Private Sub Combo6_AfterUpdate()
DoCmd.OpenReport "rptTestReport", acViewPreview
End Sub
On NoData event on the report, I would like to cancel it from opening it.
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no certificied deletate at this stage in this area. Please Cancel Report.."
Cancel = True
End Sub
However, after pressing OK on the message, I have got an error. On debug, the line would point to
DoCmd.OpenReport "rptTestReport", acViewPreview
Why is that?
Larry Larsen
Hi
Check out: Close report automatically if no data found.
Especially the error bit..
thumbup.gif
Tim
Thanks a lot Larry..
Larry Larsen
thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.