How to translate logic field values into text values

[Guest]

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Rob Oldfield]

You're better off just leaving the underlying data as it is. To get the
export format then just write a query that calculates the text string from
that base field, and export the query.

e.g. calculated field ExportYN: iif([BaseYNField],"Y","N")

 

[Guest]

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

 

[Guest]

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database
Dim rst As Recordset

Private Sub Form_Load()
Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.

After Update
With rst
.AddNew
![expLO1] = Me.chkLO1
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")
End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

--
susan

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

See notes below

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.

The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create

After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")

The above line does the translation

End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

Hi again. I'm embarrassed to appear to be dragging this out, but the "other"
part of my job has been interrupting!

I made the changes you suggested. Now I'm gettig an error saying Object
Required and .AddNew is highlighted. Any suggestions?

PS I know why you assumed I wanted a N, because that's logical. But I need a
blank.
--
susan

See notes below

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.

The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create

After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")

The above line does the translation

End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With


:

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

Move this line:
Dim rst As Recordset
To the Declarations section of your form module. I think I gave you bad
instructions.
Both the Dim dbf As Database and Dim rst As Recordset should be in the
Declarations section and neither should be anywhere else in the module.

Let me know if that fixes it.
Sorry if I misled you.

Hi again. I'm embarrassed to appear to be dragging this out, but the "other"
part of my job has been interrupting!

I made the changes you suggested. Now I'm gettig an error saying Object
Required and .AddNew is highlighted. Any suggestions?

PS I know why you assumed I wanted a N, because that's logical. But I need a
blank.
--
susan

See notes below

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.

The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create

After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")

The above line does the translation

End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.

:

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With


:

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

Do you have Option Explicit at the very top of your Declarations section?
The .AddNew is correct syntax. The problem is referencing the object. If
you don't have Option Explicit, then what is happening is the Form_Load sub
is creating the object for itself and is therefore not visible to the After
Update. If that is not it, then we need to dig deaper, but it is obviously a
scopping problem.

No apologies needed.
These were both in the Declarations:
Dim dbf As Database
Dim rst As Recordset

I removed them from the On Load event, so that it now looks like the
following:
Private Sub Form_Load()
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub

But, alas, I'm still getting Error 424 Object Required. And .AddNew is
highlighted. Is there a way to say "add new record," or am I misinterpreting
the message. I did do a Compact and Repair, hoping that might help as it
sometimes does.
--
susan

Move this line:
Dim rst As Recordset
To the Declarations section of your form module. I think I gave you bad
instructions.
Both the Dim dbf As Database and Dim rst As Recordset should be in the
Declarations section and neither should be anywhere else in the module.

Let me know if that fixes it.
Sorry if I misled you.

Hi again. I'm embarrassed to appear to be dragging this out, but the "other"
part of my job has been interrupting!

I made the changes you suggested. Now I'm gettig an error saying Object
Required and .AddNew is highlighted. Any suggestions?

PS I know why you assumed I wanted a N, because that's logical. But I need a
blank.
--
susan


:

See notes below

:

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.
The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create
After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")
The above line does the translation
End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

--
susan

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.



:

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With


:

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

See my previous post regarding the Opton Explicit

Update.
I had the declarations in the wrong place. They're now in the right place in
the module, but now I get a different error. .AddNew still highlighted. Error
91: "Object variable or With block variable not set" is the message.
--
susan

Move this line:
Dim rst As Recordset
To the Declarations section of your form module. I think I gave you bad
instructions.
Both the Dim dbf As Database and Dim rst As Recordset should be in the
Declarations section and neither should be anywhere else in the module.

Let me know if that fixes it.
Sorry if I misled you.

Hi again. I'm embarrassed to appear to be dragging this out, but the "other"
part of my job has been interrupting!

I made the changes you suggested. Now I'm gettig an error saying Object
Required and .AddNew is highlighted. Any suggestions?

PS I know why you assumed I wanted a N, because that's logical. But I need a
blank.
--
susan


:

See notes below

:

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.
The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create
After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")
The above line does the translation
End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

--
susan

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.



:

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With


:

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

Update.
I had the declarations in the wrong place. They're now in the right place in
the module, but now I get a different error. .AddNew still highlighted. Error
91: "Object variable or With block variable not set" is the message.
--
susan

Move this line:
Dim rst As Recordset
To the Declarations section of your form module. I think I gave you bad
instructions.
Both the Dim dbf As Database and Dim rst As Recordset should be in the
Declarations section and neither should be anywhere else in the module.

Let me know if that fixes it.
Sorry if I misled you.

Hi again. I'm embarrassed to appear to be dragging this out, but the "other"
part of my job has been interrupting!

I made the changes you suggested. Now I'm gettig an error saying Object
Required and .AddNew is highlighted. Any suggestions?

PS I know why you assumed I wanted a N, because that's logical. But I need a
blank.
--
susan

See notes below

:

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.
The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create
After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")
The above line does the translation
End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

--
susan

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.



:

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With


:

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

No apologies needed.
These were both in the Declarations:
Dim dbf As Database
Dim rst As Recordset

I removed them from the On Load event, so that it now looks like the
following:
Private Sub Form_Load()
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub

But, alas, I'm still getting Error 424 Object Required. And .AddNew is
highlighted. Is there a way to say "add new record," or am I misinterpreting
the message. I did do a Compact and Repair, hoping that might help as it
sometimes does.
--
susan

Move this line:
Dim rst As Recordset
To the Declarations section of your form module. I think I gave you bad
instructions.
Both the Dim dbf As Database and Dim rst As Recordset should be in the
Declarations section and neither should be anywhere else in the module.

Let me know if that fixes it.
Sorry if I misled you.

Hi again. I'm embarrassed to appear to be dragging this out, but the "other"
part of my job has been interrupting!

I made the changes you suggested. Now I'm gettig an error saying Object
Required and .AddNew is highlighted. Any suggestions?

PS I know why you assumed I wanted a N, because that's logical. But I need a
blank.
--
susan

See notes below

:

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.
The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create
After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")
The above line does the translation
End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

--
susan

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.



:

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With


:

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.

 

[Guest]

No, it said Option Compare Database. So I change it to Option Explicit, but
am still getting the new error 91 that I posted about a few minutes ago.

Sorry to be a "nudge."
--
susan

Do you have Option Explicit at the very top of your Declarations section?
The .AddNew is correct syntax. The problem is referencing the object. If
you don't have Option Explicit, then what is happening is the Form_Load sub
is creating the object for itself and is therefore not visible to the After
Update. If that is not it, then we need to dig deaper, but it is obviously a
scopping problem.

No apologies needed.
These were both in the Declarations:
Dim dbf As Database
Dim rst As Recordset

I removed them from the On Load event, so that it now looks like the
following:
Private Sub Form_Load()
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub

But, alas, I'm still getting Error 424 Object Required. And .AddNew is
highlighted. Is there a way to say "add new record," or am I misinterpreting
the message. I did do a Compact and Repair, hoping that might help as it
sometimes does.
--
susan

Move this line:
Dim rst As Recordset
To the Declarations section of your form module. I think I gave you bad
instructions.
Both the Dim dbf As Database and Dim rst As Recordset should be in the
Declarations section and neither should be anywhere else in the module.

Let me know if that fixes it.
Sorry if I misled you.

:

Hi again. I'm embarrassed to appear to be dragging this out, but the "other"
part of my job has been interrupting!

I made the changes you suggested. Now I'm gettig an error saying Object
Required and .AddNew is highlighted. Any suggestions?

PS I know why you assumed I wanted a N, because that's logical. But I need a
blank.
--
susan


:

See notes below

:

Well, I've not made any headway yet.
BTS Yes, multiple sessions, multiple users
After Update for the form does make more sense than the event for the control

But, I am getting an error highlighting the "With rst" in the After Update
for the form. It could well be that I did not properly translate "SomeField"
and "ALogicField", etc.
Here's what I have put in the various places you indicated.

tbl_EXA_Export is my transfer table. expLO1 is the first field in this table
to receive a Y value.

Module
Option Compare Database
Dim dbf As Database <---This is the correct place to put these lines
Dim rst As Recordset

Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbf.OpenDynaset)
End Sub
(Tried dbOpenDynaset, but then wondered if the syntax shouldn't be the same
as opening the recordset.
The syntax was correct. dbOpenDynaset is a constant that tells jet what kind
of recordset to create
After Update <---Remove this line
With rst
.AddNew
![expLO1] = Me.chkLO1 <---Remove this Line
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y", "")
The above line does the translation
End With
Didn't make sense to me (nor did it work -- I tried) to put the field name
bound to the control chkLO1 as = to the Iff statement. Also, got error
message that End With was expected.

Any suggestions?

--
susan

So, here it is with your names:
Private Sub Form_Load()> Dim dbf As Database
Dim rst As Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("tbl_EXA_Export", dbOpenDynaset)
End Sub


Private Sub Form_AfterUpdate()
With rst
.AddNew
![expLO1] = IIf(Me.cboExAction = "Enable" And Me!chkLO1 = True, "Y",
"")
.Update
End With

Add whatever other fields are in the database.
I notice in your version of the code you do not set expLO1 to "N". I
thought that is what you wanted.



:

I can see no reason to do it at data entry time. It does not matter what is
stored in your temporary table. It only matters what the final result is.
You could do it that way, but it will require a lot of work. If you want to
create new data each time, I would suggest you create your transfer table
with the structure you need for the export.
One thing to consider first is whether data can be added to this table in
multiple sessions or by multiple users. If so, the data should be cleared
after the export is executed.
Then, the time to update this table is not at the control level, but in the
After Update event of your form.
First define your database and recordset objects at the form's module level
so they are visible to all subs and functions in the module.
Then in the Load event of the form, establish the object references:
Dim dbf as Database
Dim rst as Recordset
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Now in the After Update event of the form:
With rst
.AddNew
![SomeField] = Me.SomeControl
![ALogicField] = Iif(Me.chkCheckBox1, "Y", "N
![AnotherField = Me.AnotherControl
.Update
End With


:

I appreciate your responses (Rob & Klatuu), but I really want to "translate"
the data and store the translation in a separate table as it is entered by
users and then delete the data when the export is completed. (The underlying
data/original table will not be changed.) This will ensure that we never
export duplicate data. If you can advise me on the code to do that, I would
very much appreciate it.
--
susan


:

My suggestion would be to use a query to do the export and convert the value
there.
In the field name row of the query create an expression that does the
conversion.

Exp1: =Iif([MyLogicFieldName] = True, "Y", "N")
You can change Exp1 to be whatever name you would like the field name to be
when it is exported. It can't be the actual name of your logic field.

:

I have a table with a logic field and a form that uses checkboxes (7 of them)
to enter the data for a single record. Now I need to export the data in Y/N
(text) format for uploading to another database. I prefer not to change the
user interface because they are accustomed to the current form. Therefore, I
would to convert the -1/0 values to Y/N, depending upon a selection in a
combo box on the form (Enable (Y) or Disable (N)) and then append the data to
a new table for exporting. (The end product will be a fixed width text file
that will be put on a floppy and uploaded on another system that is not on
our LAN.)

My first thought was to write code in the after update event of each check
box that would convert the -1 to a Y or N, depending on whether the combo box
selection was Enable or Disable, and write the value to the "new" table. I
think that in addition to "coverting" the values, I need to use an INSERT
INTO SQL statement, but I don't know the syntax. Can anyone recommend the
proper syntax or a better way to accomplish my goal.

Here's what I've started. Needless to say, it doesn't work.

Private Sub chkLO1_AfterUpdate()
If Me!cboExAction.Value = "Enable" And Me!chkLO1 = "-1" Then
(INSERT INTO???) tbl_EXA_Export.expLO1 = "Y"
End If
End Sub
If anyone has a suggestion for the correct way to write this -- or a better
way, I would appreciate the information.