Still will not work!!!!!
Mile-O-Phile said:
There was a space at the end of the string:
strCriteria = "DepartureDate = #" & StrDtConv & "# And FlightNumber = """ & StrCriteriaMatch3 & """"
I think i better explain what i am trying to do may be you can just sort the problem - Here goes for the explanation -:
I have Db with two tables.
a) TblDailyFlightSchedule
b) TblDailyFlightPaxHistoryLog
The TblDailyFlightSchedule is created from a make table query and is updated on a daily basis. What I need to happen via a command button, is to update the TblDailyFlightPaxHistoryLog with the records from the TblDailyFlightSchedule. If the records already exist in the TblDailyFlightPaxHistoryLog then update with changes. If no records exist then add the new records to TblDailyFlightPaxHistoryLog. I have created the following code but it is not working as I expect?. The update and Addnew record criteria need to based on two fields (DepartureDate) , (FlightNumber) FlightNumber field is a string , and DepartureDate is date field.
Look Forward to your help!!!!
Public Sub ProcessDailyFltSchLog()
Dim ProcessDailyFltSchLogRst As DAO.Recordset
Dim ProcessDailyFltSchLogDbs As DAO.Database
Set ProcessDailyFltSchLogDbs = CurrentDb
Set ProcessDailyFltSchLogRst = CurrentDb.OpenRecordset("TblDailyFlightSchedule", dbOpenDynaset)
ProcessDailyFltSchLogRst.MoveFirst
Do Until ProcessDailyFltSchLogRst.EOF
With ProcessDailyFltSchLogRst
StrDtConv = Format(![DepartureDate], "DD MM YY")
StrCriteriaMatch2 = ![AirlineIataCode]
StrCriteriaMatch3 = ![FlightNumber]
StrCriteriaMatch4 = Nz(![IBFlightNumber], "")
StrCriteriaMatch5 = Nz(![DepartureTime], "0:00")
StrCriteriaMatch6 = Nz(![Destination], "NA")
UpdatePDFLx
.MoveNext
End With
Loop
Set ProcessDailyFltSchLogDbs = Nothing
Set ProcessDailyFltSchLogRst = Nothing
End Sub
Public Sub UpdatePDFLx()
Dim dbsUpdatePDFL As DAO.Database
Dim RstUpdatePDFL As DAO.Recordset
Dim StrCriteriax As String
Set dbsUpdatePDFL = CurrentDb
Set RstUpdatePDFL = CurrentDb.OpenRecordset("TblDailyFlightPaxHistoryLog", dbOpenDynaset)
StrDtConv = Format(StrDtConv, "DD MM YY")
StrCriteriax = "DepartureDate =#" & StrDtConv & "# And FlightNumber ='" & StrCriteriaMatch3 & " '"
With RstUpdatePDFL
.MoveFirst
.FindFirst StrCriteriax
Do Until .NoMatch
RstUpdatePDFL.Edit
![DepartureDate] = DtDeptDt
![AirlineIataCode] = StrCriteriaMatch2
![FlightNumber] = StrCriteriaMatch3
![IBFlightNumber] = StrCriteriaMatch4
![DepartureTime] = StrCriteriaMatch5
![Destination] = StrCriteriaMatch6
If RstUpdatePDFL.NoMatch Then
![DepartureDate] = DtDeptDt
![AirlineIataCode] = StrCriteriaMatch2
![FlightNumber] = StrCriteriaMatch3
![IBFlightNumber] = StrCriteriaMatch4
![DepartureTime] = StrCriteriaMatch5
![Destination] = StrCriteriaMatch6
End If
.Update
.FindNext StrCriteriax
Loop
End With
