I am still trying get my data to do what it's told (or should I say do what I want it to do, not what it told to do!)
I accepted BrianWren's answer previousley as correct and could not get my database working correctly (i was obviousley doing somthing wrong). I am still learning VB code.
So over the Christmas break I have started from scratch to work a solution by myself, for a training exercise. I believe I may be almost there but am getting caught up with NULL values in the database.
The fields in my database are:
CamperID - Auto Number
Status - String
OnLineDate - Short Date
RunNumber - Integer
Chassis No - Long
Model - String
Series - String
Awning - Boolean (Checkbox)
Factory Pick Up - Boolean (Checkbox)
Order No - Long
Dealer - String
Customer - String
OffLineDate - Short Date
The code I currently have is as follows
--------------
Private Sub Testing_Click()
'Testing Moving columns up
Dim CamperID As Integer
Dim Status As String
Dim OnLineDate As Date
Dim RunNumber As Long
Dim ChassisNo As String
Dim Model As String
Dim Series As String
Dim Awning As Boolean
Dim Pickup As Boolean
Dim OrderNo As Long
Dim Dealer As String
Dim Customer As String
Dim Offline As Date
Dim db As Database
Dim recordsource As Recordset
Dim s As String
Set db = CurrentDb
CamperID = 205
' This CamperID Number is only for testing, the variable will come from an input box on a form.
s = "SELECT * FROM [Camper Schedule] " & _
" ORDER BY RunNumber"
Set recordsource = db.OpenRecordset(s)
recordsource.MoveFirst
Do Until recordsource!CamperID = CamperID
recordsource.MoveNext
Loop
If recordsource.EOF = True Then
recordsource.MoveLast
recordsource.Delete
Else
If recordsource!RunNumber = Null Then
recordsource.MoveNext
Else
recordsource.MoveNext
If recordsource!OnLineDate <> Null Then OnLineDate = recordsource!OnLineDate
If recordsource!RunNumber <> Null Then RunNumber = recordsource!RunNumber
If recordsource![Chassis No] <> Null Then ChassisNo = recordsource![Chassis No]
If recordsource!Model <> Null Then Model = recordsource!Model
If recordsource!Series <> Null Then Series = recordsource!Series
If recordsource!Awning <> Null Then Awning = recordsource!Awning
If recordsource![Factory Pick Up] <> Null Then Pickup = recordsource![Factory Pick Up]
If recordsource![Order No] <> Null Then OrderNo = recordsource![Order No]
If recordsource!Dealer <> Null Then Dealer = recordsource!Dealer
If recordsource!Customer <> Null Then Customer = recordsource!Customer
If recordsource!OffLineDate <> Null Then Offline = recordsource!OffLineDate
Debug.Print "assinging Values", OnLineDate; RunNumber; ChassisNo; Model; Series; Awning; Pickup; OrderNo; Dealer; Customer; Offline
recordsource.MovePrevious
Debug.Print "Original Values", recordsource!OnLineDate; recordsource!RunNumber; recordsource![Chassis No]; recordsource!Model; recordsource!Series; recordsource!Awning; recordsource![Factory Pick Up]; recordsource![Order No]; recordsource!Dealer; recordsource!Customer; recordsource!OffLineDate
With recordsource
.Edit
!RunNumber = RunNumber
![Chassis No] = ChassisNo
!Model = Model
!Series = Series
!Awning = Awning
![Factory Pick Up] = Pickup
![Order No] = OrderNo
!Dealer = Dealer
!Customer = Customer
!OffLineDate = Offline
.Update
End With
Debug.Print "New Values", recordsource!OnLineDate; recordsource!RunNumber; recordsource![Chassis No]; recordsource!Model; recordsource!Series; recordsource!Awning; recordsource![Factory Pick Up]; recordsource![Order No]; recordsource!Dealer; recordsource!Customer; recordsource!OffLineDate
MsgBox "Check"
recordsource.MoveNext
End If
End If
MsgBox "end sub"
End Sub
-----------------
I would be greatful for any advice. And if need be I can eMail a copy of the database.
Cheers
SW
