code problem for export Flag

[eggwater]

hi folks

I currently have the following code on a form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me
If .Dirty Then
If .NewRecord Then
.date_stamp = Now()
.Export_ctrl = "1"
Else
.date_stamp = Now()
.Export_ctrl = "2"
End If
End If
End With
End Sub

This automatically generates data in two fields depending on whether the record is New or Amended.

My problem is that I have two types of export criteria NEW and AMENDED - the above code works - but I have another field ROOM NUMBER that also plays a part in whether the record is exported or not - i.e. if no ROOM NUMBER exists then the record is not exported.

However if a Room Number is not entered in a New record till a later date the record then becomes Status AMENDED when it hasn't actually been exported yet. I need the code to work so that new unexported records remain that way until they have been exported.

can anyone help?

 

[Uncle Gizmo]

You currently have two conditions, new or amended what if you added another condition “not complete” give it the flag number three. So you need to check if there is a room number and if the record has been amended, if the record is amended, but there’s no room number give its flag three, however if it’s been amended and there’s a room number give its flag two.

 

[Fornatian]

You don't need to check the Dirty property, the before update event is only triggered for dirty records.

Also the setting of the datestamp to Now() doesn't seem to relate to whether the record is new or not, therefore it should be outside the conditional actions as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If .NewRecord Then

If IsNull(.RoomNumberField) then
.Export_ctrl = "3" ' a new status for incomplete records
Else
.Export_ctrl = "1" ' new and completed
End if

Else

.Export_ctrl = "2" 'amended
End If

.DateStamp = Now()
End With

End Sub


Using this approach you can run a query using Export_ctrl criteria to catch those records incomplete.

 

[eggwater]

thanks but I have a problem

I have used this code and I can see the logic behind it yet I have the following problem that may hinder the export action

If i enter a new record with no room number my export_ctrl field says "New un_Allocated Room"

If I then allocate a room to this at a later time the field changes to "Amended Record" I need all new Room Allocations to be in the same export file and all amended room allocations to be in a separate export file...

i.e. assuming that amended records have actually been exported at some point as "New rm Allocated"

I'll keep at it

if you can help - please do

 

[eggwater]

On Monday the 1st of September Robert Hyde (self proclaimed intellectual) wrote:

Can I add another IF

e.g. If .export_ctrl = "3" And IsNotNull(.room_number)
Then .export_ctrl = "1"

I've tried this but there's something wrong with the syntax.



*says to himself "I am getting better at this VBA lark, honest"*

 

[Fornatian]

So what you are saying is you need another status field for updated room number records?

 

[eggwater]

final solution

this seemed to work if anyone's interested:

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me



If .NewRecord Then
If IsNull(Room_Number) Then
.date_stamp = Now()
.Export_ctrl = "4"
Else
If Not IsNull(Room_Number) Then
.Export_ctrl = "1"
.date_stamp = Now()
End If
End If
Else
If .Export_ctrl = "4" And Not IsNull(Room_Number) Then
.date_stamp = Now()
.Export_ctrl = "1"

Else
If .Export_ctrl = "3" Then
.date_stamp = Now()
.Export_ctrl = "2"
End If
End If
End If


End With
End Sub


Thanks to those who pointed me in the right direction