Using Wend to assign form object values to variables.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have form that allows users to enter data in a spread sheet format, bu I
save in a normalized format.

There are twenty columns. The user can select the column headings. The
objects and fields are numerically ordered.

[Forms]![frmCoC]![Lot Entry Form]![cmbAtt1],
[Forms]![frmCoC]![Lot Entry Form]![cmbAtt2]

FT1, FT2,

I use a while statment to step through each and save as shown below.

aVar = 2
While aVar < 21
qVar = "INSERT .WHERE (((tblZHoldFiberLotNumber.FT" & aVar & ") Is Not
Null));"
DoCmd.RunSQL qVar

aVar = aVar + 1
Wend

This works fine, but I want to know beforehand how many non null column
headings there are. Then I can elimenate any columns left blank. For example
if all columns are used except column #5, currently I would save 1-4, and
6-20. I want to renumber and save 1-19 intead. I would like to use a Whlie
statement to step through each object rather than looking at each
individually. I am using

Check =1
aVar = 1
While avar <21
qVar = [Forms]![frmCoC]![Lot Entry Form]![cmbAtt" & avar & "]"
If IsNull(qVar) = False Then
Check = Check +1
EndIF
Wend

This assigns qVar the literal string value instead of the form value. What
would be the correct syntacts?

Thanks, any help would be appreciated.
 
On first glance it looks to me like you can just test if the column is null
and skip the update and the increment of the variable and all will be
beautiful, but I am ever the optimist.
 
That is what I do now, but I want to be able to condence thie column values
if the heading is null. Like the example I gave instead of recording 1-4 and
6-20 when column 5 is null I want to record as 1-19.

David F Cox said:
On first glance it looks to me like you can just test if the column is null
and skip the update and the increment of the variable and all will be
beautiful, but I am ever the optimist.


Justin said:
I have form that allows users to enter data in a spread sheet format, bu I
save in a normalized format.

There are twenty columns. The user can select the column headings. The
objects and fields are numerically ordered.

[Forms]![frmCoC]![Lot Entry Form]![cmbAtt1],
[Forms]![frmCoC]![Lot Entry Form]![cmbAtt2]

FT1, FT2,

I use a while statment to step through each and save as shown below.

aVar = 2
While aVar < 21
qVar = "INSERT .WHERE (((tblZHoldFiberLotNumber.FT" & aVar & ") Is Not
Null));"
DoCmd.RunSQL qVar

aVar = aVar + 1
Wend

This works fine, but I want to know beforehand how many non null column
headings there are. Then I can elimenate any columns left blank. For
example
if all columns are used except column #5, currently I would save 1-4, and
6-20. I want to renumber and save 1-19 intead. I would like to use a
Whlie
statement to step through each object rather than looking at each
individually. I am using

Check =1
aVar = 1
While avar <21
qVar = [Forms]![frmCoC]![Lot Entry Form]![cmbAtt" & avar & "]"
If IsNull(qVar) = False Then
Check = Check +1
EndIF
Wend

This assigns qVar the literal string value instead of the form value.
What
would be the correct syntacts?

Thanks, any help would be appreciated.
Click to expand...
Click to expand...
 
I do not think that is what you do now.

If column is null then
' nothing to do

else
update field
avar = avar+1
endif


Justin said:
That is what I do now, but I want to be able to condence thie column
values
if the heading is null. Like the example I gave instead of recording 1-4
and
6-20 when column 5 is null I want to record as 1-19.

David F Cox said:
On first glance it looks to me like you can just test if the column is
null
and skip the update and the increment of the variable and all will be
beautiful, but I am ever the optimist.


Justin said:
I have form that allows users to enter data in a spread sheet format, bu
I
save in a normalized format.

There are twenty columns. The user can select the column headings. The
objects and fields are numerically ordered.

[Forms]![frmCoC]![Lot Entry Form]![cmbAtt1],
[Forms]![frmCoC]![Lot Entry Form]![cmbAtt2]

FT1, FT2,

I use a while statment to step through each and save as shown below.

aVar = 2
While aVar < 21
qVar = "INSERT .WHERE (((tblZHoldFiberLotNumber.FT" & aVar & ") Is Not
Null));"
DoCmd.RunSQL qVar

aVar = aVar + 1
Wend

This works fine, but I want to know beforehand how many non null column
headings there are. Then I can elimenate any columns left blank. For
example
if all columns are used except column #5, currently I would save 1-4,
and
6-20. I want to renumber and save 1-19 intead. I would like to use a
Whlie
statement to step through each object rather than looking at each
individually. I am using

Check =1
aVar = 1
While avar <21
qVar = [Forms]![frmCoC]![Lot Entry Form]![cmbAtt" & avar & "]"
If IsNull(qVar) = False Then
Check = Check +1
EndIF
Wend

This assigns qVar the literal string value instead of the form value.
What
would be the correct syntacts?

Thanks, any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I use the variable avar to step across the objects so I need to increment
this. I guess what my problem comes down to is how to assign a value to a
variable from a object name that I created using code

qVar = [Forms]![frmCoC]![Lot Entry Form]![cmbAtt" & aVar &"]"
If IsNull(qVar) = False Then...

How do I get qVar to be equalt to the value of the object ([cmbAtt1],
[cmbAtt2],etc) instead of the literal value "Forms![frmCoC]![Lot Entry
Form]![cmbAtt1], Forms![frmCoC]![Lot Entry Form]![cmbAtt2],etc)



David F Cox said:
I do not think that is what you do now.

If column is null then
' nothing to do

else
update field
avar = avar+1
endif


Justin said:
That is what I do now, but I want to be able to condence thie column
values
if the heading is null. Like the example I gave instead of recording 1-4
and
6-20 when column 5 is null I want to record as 1-19.

David F Cox said:
On first glance it looks to me like you can just test if the column is
null
and skip the update and the increment of the variable and all will be
beautiful, but I am ever the optimist.


I have form that allows users to enter data in a spread sheet format, bu
I
save in a normalized format.

There are twenty columns. The user can select the column headings. The
objects and fields are numerically ordered.

[Forms]![frmCoC]![Lot Entry Form]![cmbAtt1],
[Forms]![frmCoC]![Lot Entry Form]![cmbAtt2]

FT1, FT2,

I use a while statment to step through each and save as shown below.

aVar = 2
While aVar < 21
qVar = "INSERT .WHERE (((tblZHoldFiberLotNumber.FT" & aVar & ") Is Not
Null));"
DoCmd.RunSQL qVar

aVar = aVar + 1
Wend

This works fine, but I want to know beforehand how many non null column
headings there are. Then I can elimenate any columns left blank. For
example
if all columns are used except column #5, currently I would save 1-4,
and
6-20. I want to renumber and save 1-19 intead. I would like to use a
Whlie
statement to step through each object rather than looking at each
individually. I am using

Check =1
aVar = 1
While avar <21
qVar = [Forms]![frmCoC]![Lot Entry Form]![cmbAtt" & avar & "]"
If IsNull(qVar) = False Then
Check = Check +1
EndIF
Wend

This assigns qVar the literal string value instead of the form value.
What
would be the correct syntacts?

Thanks, any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
My apologies. I now think that you need a separate variable for the output
which does not get incremented for null values.
avar=1
ouputvar=1
while ...

if null ....

else
....
outputvar = outputvar+1
endif

avar = avar + 1

Wend

how does that look to you?



Justin said:
I use the variable avar to step across the objects so I need to increment
this. I guess what my problem comes down to is how to assign a value to a
variable from a object name that I created using code

qVar = [Forms]![frmCoC]![Lot Entry Form]![cmbAtt" & aVar &"]"
If IsNull(qVar) = False Then...

How do I get qVar to be equalt to the value of the object ([cmbAtt1],
[cmbAtt2],etc) instead of the literal value "Forms![frmCoC]![Lot Entry
Form]![cmbAtt1], Forms![frmCoC]![Lot Entry Form]![cmbAtt2],etc)



David F Cox said:
I do not think that is what you do now.

If column is null then
' nothing to do

else
update field
avar = avar+1
endif


Justin said:
That is what I do now, but I want to be able to condence thie column
values
if the heading is null. Like the example I gave instead of recording
1-4
and
6-20 when column 5 is null I want to record as 1-19.

:

On first glance it looks to me like you can just test if the column is
null
and skip the update and the increment of the variable and all will be
beautiful, but I am ever the optimist.


I have form that allows users to enter data in a spread sheet format,
bu
I
save in a normalized format.

There are twenty columns. The user can select the column headings.
The
objects and fields are numerically ordered.

[Forms]![frmCoC]![Lot Entry Form]![cmbAtt1],
[Forms]![frmCoC]![Lot Entry Form]![cmbAtt2]

FT1, FT2,

I use a while statment to step through each and save as shown below.

aVar = 2
While aVar < 21
qVar = "INSERT .WHERE (((tblZHoldFiberLotNumber.FT" & aVar & ") Is
Not
Null));"
DoCmd.RunSQL qVar

aVar = aVar + 1
Wend

This works fine, but I want to know beforehand how many non null
column
headings there are. Then I can elimenate any columns left blank.
For
example
if all columns are used except column #5, currently I would save
1-4,
and
6-20. I want to renumber and save 1-19 intead. I would like to use
a
Whlie
statement to step through each object rather than looking at each
individually. I am using

Check =1
aVar = 1
While avar <21
qVar = [Forms]![frmCoC]![Lot Entry Form]![cmbAtt" & avar & "]"
If IsNull(qVar) = False Then
Check = Check +1
EndIF
Wend

This assigns qVar the literal string value instead of the form
value.
What
would be the correct syntacts?

Thanks, any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need combo box to refresh when moving to new record 2017 1
You Can't Assign A Value To This Object 2
Returning variables from external form 9
can't assign value to this object 1
Can't assign a value to object 6
Function to show all values 4
Cant assign value to object 1
using NZ() function to return ''0'' value when query is null 1

Back
Top