Need to save a field that was assembled on a form.

[Michael]

This is kind of a cross post from my own post of a few days ago, I have the
code that is presented below that gives me a number based on some choices
made in different drop down list.

I know the conventional wisdom is to not save information you already have
in the database. but I need this field to print and read barcodes on other
forms and reports.

I have this code as my control source on the text box and I would like to
save the results in a field in the table this form is derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[Guest]

Part of the conventional wisdom is also to use the calculations for any
displaying or report printing you may need to do. The best technique for
that is to put your calculations in a function in a standard module. Call
the funtion any time you need to display the calculated value. That way, all
calculations are consistent and you only have one place to have to change it
when it becomes necessary.

 

[BruceM]

I assume that you do not want the number changing when you view the record
from day to day, in which case it makes sense to save it. You could save it
by using a command button, or in the after update event of a combo box. Do
you want the value to change when the date changes or when a combo box value
changes, or is this a value that is to be created when the record is
created, after which it is to remain the same?

 

[Michael]

Thank Bruce,
This number will always remain the same. It becomes the ID number for this
pallet and the product that is on the pallet. For this reason I would like
to keep the number with the other information on this form. I know I can
recreated it when needed, but since it is a number that will never change
with any calculation I just thought it would be easier to keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the barcode
and also later on in the process it will be used to track the product after
it has been processed and packaged and sent to stores.

michael

I assume that you do not want the number changing when you view the record
from day to day, in which case it makes sense to save it. You could save
it by using a command button, or in the after update event of a combo box.
Do you want the value to change when the date changes or when a combo box
value changes, or is this a value that is to be created when the record is
created, after which it is to remain the same?

This is kind of a cross post from my own post of a few days ago, I have
the code that is presented below that gives me a number based on some
choices made in different drop down list.

I know the conventional wisdom is to not save information you already
have in the database. but I need this field to print and read barcodes on
other forms and reports.

I have this code as my control source on the text box and I would like to
save the results in a field in the table this form is derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[BruceM]

So you are saying that the number is to be created based on criteria such as
date, and thereafter the number that was first created for the record will
remain a part of that record. In this case storing the value is legitimate.
You cannot calculate it when needed based on date unless you are prepared
for the number to change every time you view the record or print a report.
Your number is based in part on combo box selections, it seems. That's OK,
but do you need the number to change if those selections ever change? Will
they ever change, as for instance if there is an error in the original
record, or for any other reason?
By the way, you can re-create it as needed only if there is a date field in
the original record, and if that and the content of the combo boxes will not
change.
To create the number you could use the after update event of one of the
combo boxes (untested air code):

If IsNull (Me.MyTextBox) then
Me.MyTextBox = Format(DatePart("ww"; Date; vbMonday; vbFirstFullWeek);
"00") & _
Format(DatePart("w"; Date; vbMonday); "00") " " &
Format([Fornitore].Column(1);"00") & _
" " & Format([Prodotto].Column(1);"000")
End If

This assumes that MyTextBox is a text box bound to the field in which you
would store the number. Note that the underscores are used only for line
wrapping in the code window, for convenience of viewing the code. By the
way, I don't how to translate the "3" for week of the year to VBA, so I
added vbFirstFullWeek and left it at that. Note that Column is not enclosed
in square brackets.

Thank Bruce,
This number will always remain the same. It becomes the ID number for this
pallet and the product that is on the pallet. For this reason I would like
to keep the number with the other information on this form. I know I can
recreated it when needed, but since it is a number that will never change
with any calculation I just thought it would be easier to keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the barcode
and also later on in the process it will be used to track the product
after it has been processed and packaged and sent to stores.

michael

I assume that you do not want the number changing when you view the record
from day to day, in which case it makes sense to save it. You could save
it by using a command button, or in the after update event of a combo box.
Do you want the value to change when the date changes or when a combo box
value changes, or is this a value that is to be created when the record is
created, after which it is to remain the same?

This is kind of a cross post from my own post of a few days ago, I have
the code that is presented below that gives me a number based on some
choices made in different drop down list.

I know the conventional wisdom is to not save information you already
have in the database. but I need this field to print and read barcodes
on other forms and reports.

I have this code as my control source on the text box and I would like
to save the results in a field in the table this form is derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[Michael]

Thank you for the code,

The code will never change. There is only one product that can be on a
pallet and that product will never change.

What I mean is that pallet #2002 is Lettuce.
The code that this will create tells me that it was received on the 44th
week on the first day of the week (Monday), and that the farmer who brought
the product is Mr. Jones (2) and that the product is lettuce. (70000) so the
code will be 4401 2 7000 this code will never change and if there ever is a
problem at a store with this lettuce, we can say where this product came
from on which day.

I will work on this first thing in the morning and see if I can get it to
work..
take care and thank you again

Michael

So you are saying that the number is to be created based on criteria such
as date, and thereafter the number that was first created for the record
will remain a part of that record. In this case storing the value is
legitimate. You cannot calculate it when needed based on date unless you
are prepared for the number to change every time you view the record or
print a report.
Your number is based in part on combo box selections, it seems. That's
OK, but do you need the number to change if those selections ever change?
Will they ever change, as for instance if there is an error in the
original record, or for any other reason?
By the way, you can re-create it as needed only if there is a date field
in the original record, and if that and the content of the combo boxes
will not change.
To create the number you could use the after update event of one of the
combo boxes (untested air code):

If IsNull (Me.MyTextBox) then
Me.MyTextBox = Format(DatePart("ww"; Date; vbMonday; vbFirstFullWeek);
"00") & _
Format(DatePart("w"; Date; vbMonday); "00") " " &
Format([Fornitore].Column(1);"00") & _
" " & Format([Prodotto].Column(1);"000")
End If

This assumes that MyTextBox is a text box bound to the field in which you
would store the number. Note that the underscores are used only for line
wrapping in the code window, for convenience of viewing the code. By the
way, I don't how to translate the "3" for week of the year to VBA, so I
added vbFirstFullWeek and left it at that. Note that Column is not
enclosed in square brackets.

Thank Bruce,
This number will always remain the same. It becomes the ID number for
this pallet and the product that is on the pallet. For this reason I
would like to keep the number with the other information on this form. I
know I can recreated it when needed, but since it is a number that will
never change with any calculation I just thought it would be easier to
keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the barcode
and also later on in the process it will be used to track the product
after it has been processed and packaged and sent to stores.

michael

I assume that you do not want the number changing when you view the
record from day to day, in which case it makes sense to save it. You
could save it by using a command button, or in the after update event of
a combo box. Do you want the value to change when the date changes or
when a combo box value changes, or is this a value that is to be created
when the record is created, after which it is to remain the same?

This is kind of a cross post from my own post of a few days ago, I have
the code that is presented below that gives me a number based on some
choices made in different drop down list.

I know the conventional wisdom is to not save information you already
have in the database. but I need this field to print and read barcodes
on other forms and reports.

I have this code as my control source on the text box and I would like
to save the results in a field in the table this form is derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[BruceM]

Glad to help. Post back if it gives you problems.

Thank you for the code,

The code will never change. There is only one product that can be on a
pallet and that product will never change.

What I mean is that pallet #2002 is Lettuce.
The code that this will create tells me that it was received on the 44th
week on the first day of the week (Monday), and that the farmer who
brought the product is Mr. Jones (2) and that the product is lettuce.
(70000) so the code will be 4401 2 7000 this code will never change and if
there ever is a problem at a store with this lettuce, we can say where
this product came from on which day.

I will work on this first thing in the morning and see if I can get it to
work..
take care and thank you again

Michael

So you are saying that the number is to be created based on criteria such
as date, and thereafter the number that was first created for the record
will remain a part of that record. In this case storing the value is
legitimate. You cannot calculate it when needed based on date unless you
are prepared for the number to change every time you view the record or
print a report.
Your number is based in part on combo box selections, it seems. That's
OK, but do you need the number to change if those selections ever change?
Will they ever change, as for instance if there is an error in the
original record, or for any other reason?
By the way, you can re-create it as needed only if there is a date field
in the original record, and if that and the content of the combo boxes
will not change.
To create the number you could use the after update event of one of the
combo boxes (untested air code):

If IsNull (Me.MyTextBox) then
Me.MyTextBox = Format(DatePart("ww"; Date; vbMonday; vbFirstFullWeek);
"00") & _
Format(DatePart("w"; Date; vbMonday); "00") " " &
Format([Fornitore].Column(1);"00") & _
" " & Format([Prodotto].Column(1);"000")
End If

This assumes that MyTextBox is a text box bound to the field in which you
would store the number. Note that the underscores are used only for line
wrapping in the code window, for convenience of viewing the code. By the
way, I don't how to translate the "3" for week of the year to VBA, so I
added vbFirstFullWeek and left it at that. Note that Column is not
enclosed in square brackets.

Thank Bruce,
This number will always remain the same. It becomes the ID number for
this pallet and the product that is on the pallet. For this reason I
would like to keep the number with the other information on this form. I
know I can recreated it when needed, but since it is a number that will
never change with any calculation I just thought it would be easier to
keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the
barcode and also later on in the process it will be used to track the
product after it has been processed and packaged and sent to stores.

michael


"BruceM" <[email protected]> ha scritto nel messaggio
I assume that you do not want the number changing when you view the
record from day to day, in which case it makes sense to save it. You
could save it by using a command button, or in the after update event of
a combo box. Do you want the value to change when the date changes or
when a combo box value changes, or is this a value that is to be created
when the record is created, after which it is to remain the same?

This is kind of a cross post from my own post of a few days ago, I
have the code that is presented below that gives me a number based on
some choices made in different drop down list.

I know the conventional wisdom is to not save information you already
have in the database. but I need this field to print and read barcodes
on other forms and reports.

I have this code as my control source on the text box and I would like
to save the results in a field in the table this form is derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[Michael]

Bruce, In the expression builder I pasted the code and changed the name of
the text box. I placed it in the after update in the dropdown of the
prodotto. When I try to run the program, I get and error message that MS
Office Access can't find the macro 'If isNull(Me.'

thanks again for your help
michael

So you are saying that the number is to be created based on criteria such
as date, and thereafter the number that was first created for the record
will remain a part of that record. In this case storing the value is
legitimate. You cannot calculate it when needed based on date unless you
are prepared for the number to change every time you view the record or
print a report.
Your number is based in part on combo box selections, it seems. That's
OK, but do you need the number to change if those selections ever change?
Will they ever change, as for instance if there is an error in the
original record, or for any other reason?
By the way, you can re-create it as needed only if there is a date field
in the original record, and if that and the content of the combo boxes
will not change.
To create the number you could use the after update event of one of the
combo boxes (untested air code):

If IsNull (Me.MyTextBox) then
Me.MyTextBox = Format(DatePart("ww"; Date; vbMonday; vbFirstFullWeek);
"00") & _
Format(DatePart("w"; Date; vbMonday); "00") " " &
Format([Fornitore].Column(1);"00") & _
" " & Format([Prodotto].Column(1);"000")
End If

This assumes that MyTextBox is a text box bound to the field in which you
would store the number. Note that the underscores are used only for line
wrapping in the code window, for convenience of viewing the code. By the
way, I don't how to translate the "3" for week of the year to VBA, so I
added vbFirstFullWeek and left it at that. Note that Column is not
enclosed in square brackets.

Thank Bruce,
This number will always remain the same. It becomes the ID number for
this pallet and the product that is on the pallet. For this reason I
would like to keep the number with the other information on this form. I
know I can recreated it when needed, but since it is a number that will
never change with any calculation I just thought it would be easier to
keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the barcode
and also later on in the process it will be used to track the product
after it has been processed and packaged and sent to stores.

michael

I assume that you do not want the number changing when you view the
record from day to day, in which case it makes sense to save it. You
could save it by using a command button, or in the after update event of
a combo box. Do you want the value to change when the date changes or
when a combo box value changes, or is this a value that is to be created
when the record is created, after which it is to remain the same?

This is kind of a cross post from my own post of a few days ago, I have
the code that is presented below that gives me a number based on some
choices made in different drop down list.

I know the conventional wisdom is to not save information you already
have in the database. but I need this field to print and read barcodes
on other forms and reports.

I have this code as my control source on the text box and I would like
to save the results in a field in the table this form is derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[Michael]

Hi.. I dont know why I had posted vb script in the expression builder, but
when I enter it in a module I get an error that states "compile error: End
If without block If"

michael
ps I renamed my txt box to match the code in case the error was on my part
so this is how the code reads now.

Private Sub Prodotto_AfterUpdate()
If IsNull(Me.MyTextBox) Then Me.MyTextBox = Format(DatePart("ww", Date,
vbMonday, vbFirstFullWeek), "00") & Format(DatePart("w", Date, vbMonday),
"00") & " " & Format([Fornitore].Column(1), "00") & " " &
Format([prodotto].Column(1), "000")
End If
End Sub

Bruce, In the expression builder I pasted the code and changed the name
of the text box. I placed it in the after update in the dropdown of the
prodotto. When I try to run the program, I get and error message that MS
Office Access can't find the macro 'If isNull(Me.'

thanks again for your help
michael

So you are saying that the number is to be created based on criteria such
as date, and thereafter the number that was first created for the record
will remain a part of that record. In this case storing the value is
legitimate. You cannot calculate it when needed based on date unless you
are prepared for the number to change every time you view the record or
print a report.
Your number is based in part on combo box selections, it seems. That's
OK, but do you need the number to change if those selections ever change?
Will they ever change, as for instance if there is an error in the
original record, or for any other reason?
By the way, you can re-create it as needed only if there is a date field
in the original record, and if that and the content of the combo boxes
will not change.
To create the number you could use the after update event of one of the
combo boxes (untested air code):

If IsNull (Me.MyTextBox) then
Me.MyTextBox = Format(DatePart("ww"; Date; vbMonday; vbFirstFullWeek);
"00") & _
Format(DatePart("w"; Date; vbMonday); "00") " " &
Format([Fornitore].Column(1);"00") & _
" " & Format([Prodotto].Column(1);"000")
End If

This assumes that MyTextBox is a text box bound to the field in which you
would store the number. Note that the underscores are used only for line
wrapping in the code window, for convenience of viewing the code. By the
way, I don't how to translate the "3" for week of the year to VBA, so I
added vbFirstFullWeek and left it at that. Note that Column is not
enclosed in square brackets.

Thank Bruce,
This number will always remain the same. It becomes the ID number for
this pallet and the product that is on the pallet. For this reason I
would like to keep the number with the other information on this form. I
know I can recreated it when needed, but since it is a number that will
never change with any calculation I just thought it would be easier to
keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the
barcode and also later on in the process it will be used to track the
product after it has been processed and packaged and sent to stores.

michael


"BruceM" <[email protected]> ha scritto nel messaggio
I assume that you do not want the number changing when you view the
record from day to day, in which case it makes sense to save it. You
could save it by using a command button, or in the after update event of
a combo box. Do you want the value to change when the date changes or
when a combo box value changes, or is this a value that is to be created
when the record is created, after which it is to remain the same?

This is kind of a cross post from my own post of a few days ago, I
have the code that is presented below that gives me a number based on
some choices made in different drop down list.

I know the conventional wisdom is to not save information you already
have in the database. but I need this field to print and read barcodes
on other forms and reports.

I have this code as my control source on the text box and I would like
to save the results in a field in the table this form is derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[Michael]

got it.. had made an error in the format
thanks again
michael

Hi.. I dont know why I had posted vb script in the expression builder, but
when I enter it in a module I get an error that states "compile error: End
If without block If"

michael
ps I renamed my txt box to match the code in case the error was on my part
so this is how the code reads now.

Private Sub Prodotto_AfterUpdate()
If IsNull(Me.MyTextBox) Then Me.MyTextBox = Format(DatePart("ww", Date,
vbMonday, vbFirstFullWeek), "00") & Format(DatePart("w", Date, vbMonday),
"00") & " " & Format([Fornitore].Column(1), "00") & " " &
Format([prodotto].Column(1), "000")
End If
End Sub

Bruce, In the expression builder I pasted the code and changed the name
of the text box. I placed it in the after update in the dropdown of the
prodotto. When I try to run the program, I get and error message that MS
Office Access can't find the macro 'If isNull(Me.'

thanks again for your help
michael

So you are saying that the number is to be created based on criteria
such as date, and thereafter the number that was first created for the
record will remain a part of that record. In this case storing the
value is legitimate. You cannot calculate it when needed based on date
unless you are prepared for the number to change every time you view the
record or print a report.
Your number is based in part on combo box selections, it seems. That's
OK, but do you need the number to change if those selections ever
change? Will they ever change, as for instance if there is an error in
the original record, or for any other reason?
By the way, you can re-create it as needed only if there is a date field
in the original record, and if that and the content of the combo boxes
will not change.
To create the number you could use the after update event of one of the
combo boxes (untested air code):

If IsNull (Me.MyTextBox) then
Me.MyTextBox = Format(DatePart("ww"; Date; vbMonday;
vbFirstFullWeek); "00") & _
Format(DatePart("w"; Date; vbMonday); "00") " " &
Format([Fornitore].Column(1);"00") & _
" " & Format([Prodotto].Column(1);"000")
End If

This assumes that MyTextBox is a text box bound to the field in which
you would store the number. Note that the underscores are used only for
line wrapping in the code window, for convenience of viewing the code.
By the way, I don't how to translate the "3" for week of the year to
VBA, so I added vbFirstFullWeek and left it at that. Note that Column
is not enclosed in square brackets.



Thank Bruce,
This number will always remain the same. It becomes the ID number for
this pallet and the product that is on the pallet. For this reason I
would like to keep the number with the other information on this form.
I know I can recreated it when needed, but since it is a number that
will never change with any calculation I just thought it would be
easier to keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the
barcode and also later on in the process it will be used to track the
product after it has been processed and packaged and sent to stores.

michael


"BruceM" <[email protected]> ha scritto nel messaggio
I assume that you do not want the number changing when you view the
record from day to day, in which case it makes sense to save it. You
could save it by using a command button, or in the after update event
of a combo box. Do you want the value to change when the date changes
or when a combo box value changes, or is this a value that is to be
created when the record is created, after which it is to remain the
same?

This is kind of a cross post from my own post of a few days ago, I
have the code that is presented below that gives me a number based on
some choices made in different drop down list.

I know the conventional wisdom is to not save information you already
have in the database. but I need this field to print and read
barcodes on other forms and reports.

I have this code as my control source on the text box and I would
like to save the results in a field in the table this form is derived
from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael

 

[BruceM]

Glad to help. Good luck with the project.

got it.. had made an error in the format
thanks again
michael

Hi.. I dont know why I had posted vb script in the expression builder,
but when I enter it in a module I get an error that states "compile
error: End If without block If"

michael
ps I renamed my txt box to match the code in case the error was on my
part so this is how the code reads now.

Private Sub Prodotto_AfterUpdate()
If IsNull(Me.MyTextBox) Then Me.MyTextBox = Format(DatePart("ww", Date,
vbMonday, vbFirstFullWeek), "00") & Format(DatePart("w", Date, vbMonday),
"00") & " " & Format([Fornitore].Column(1), "00") & " " &
Format([prodotto].Column(1), "000")
End If
End Sub

Bruce, In the expression builder I pasted the code and changed the name
of the text box. I placed it in the after update in the dropdown of the
prodotto. When I try to run the program, I get and error message that MS
Office Access can't find the macro 'If isNull(Me.'

thanks again for your help
michael





"BruceM" <[email protected]> ha scritto nel messaggio
So you are saying that the number is to be created based on criteria
such as date, and thereafter the number that was first created for the
record will remain a part of that record. In this case storing the
value is legitimate. You cannot calculate it when needed based on date
unless you are prepared for the number to change every time you view
the record or print a report.
Your number is based in part on combo box selections, it seems. That's
OK, but do you need the number to change if those selections ever
change? Will they ever change, as for instance if there is an error in
the original record, or for any other reason?
By the way, you can re-create it as needed only if there is a date
field in the original record, and if that and the content of the combo
boxes will not change.
To create the number you could use the after update event of one of the
combo boxes (untested air code):

If IsNull (Me.MyTextBox) then
Me.MyTextBox = Format(DatePart("ww"; Date; vbMonday;
vbFirstFullWeek); "00") & _
Format(DatePart("w"; Date; vbMonday); "00") " " &
Format([Fornitore].Column(1);"00") & _
" " & Format([Prodotto].Column(1);"000")
End If

This assumes that MyTextBox is a text box bound to the field in which
you would store the number. Note that the underscores are used only
for line wrapping in the code window, for convenience of viewing the
code. By the way, I don't how to translate the "3" for week of the year
to VBA, so I added vbFirstFullWeek and left it at that. Note that
Column is not enclosed in square brackets.



Thank Bruce,
This number will always remain the same. It becomes the ID number for
this pallet and the product that is on the pallet. For this reason I
would like to keep the number with the other information on this form.
I know I can recreated it when needed, but since it is a number that
will never change with any calculation I just thought it would be
easier to keep in a field.

This number will be used as the barcode for this pallet and my barcode
program requires me to assign a field that holds the value of the
barcode and also later on in the process it will be used to track the
product after it has been processed and packaged and sent to stores.

michael


"BruceM" <[email protected]> ha scritto nel messaggio
I assume that you do not want the number changing when you view the
record from day to day, in which case it makes sense to save it. You
could save it by using a command button, or in the after update event
of a combo box. Do you want the value to change when the date changes
or when a combo box value changes, or is this a value that is to be
created when the record is created, after which it is to remain the
same?

This is kind of a cross post from my own post of a few days ago, I
have the code that is presented below that gives me a number based
on some choices made in different drop down list.

I know the conventional wisdom is to not save information you
already have in the database. but I need this field to print and
read barcodes on other forms and reports.

I have this code as my control source on the text box and I would
like to save the results in a field in the table this form is
derived from.


=Format(DatePart("ww";Date();2;3);"00") &
Format(DatePart("w";Date();2);"00") & " " &
Format([Fornitore].[Column](1);"00") & " " &
Format([Prodotto].[Column](1);"000")

thank you for your help.
Michael