Wayne said:Thanks for response, I'll try to give more details.
Sandwich shop owner has a tbl of customers and sales transactions (one to
many) relationship. The owner wnats to record each transaction. With each
transaction is processed, a yes/no fld is set to yes on the cust tbl. When
transactions reach >=50.00 for a customer, the owner wants the option to
print or not print a 5.00 off next purcahse coupon. I have everything
seeming working fine until I get to the "print coupon" form. I query the
cust tbl and check the [runtot] fld for >=50.00. What I need to happen is if
[runtot] >=50.00 display the coupon form, if <50.00 return to main menu.
I seem to be close, but being new I can't seem to solve this one form or
another problem.
Crystal, thanks for your help and undersatnding.
WWV
Have a subform control that you change the SourceObject for... one
SourceObject is the first form and the other is ther second form
but this cannot be done from a table -- you must use a form -- you should
never enter data directly into tables anyway...
If you specify details of what you are doing, we can help you more
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
strive4peace said:Hi Wayne,
I do not see why you need the yes/no field in your customers table...
when you DO print a coupon, how are you indicating that in the database?
Is there a certain code you use in the transactions table? If so, you can
count the transactions since the last transaction record with that code
... or, if they have not gotten a coupon, the number of transactions.
Then, when you process a coupon, it needs to be indicated so that the
count starts again.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne said:Thanks for response, I'll try to give more details.
Sandwich shop owner has a tbl of customers and sales transactions (one to
many) relationship. The owner wnats to record each transaction. With each
transaction is processed, a yes/no fld is set to yes on the cust tbl.
When transactions reach >=50.00 for a customer, the owner wants the
option to print or not print a 5.00 off next purcahse coupon. I have
everything seeming working fine until I get to the "print coupon" form. I
query the cust tbl and check the [runtot] fld for >=50.00. What I need to
happen is if [runtot] >=50.00 display the coupon form, if <50.00 return
to main menu.
I seem to be close, but being new I can't seem to solve this one form or
another problem.
Crystal, thanks for your help and undersatnding.
WWV
Have a subform control that you change the SourceObject for... one
SourceObject is the first form and the other is ther second form
but this cannot be done from a table -- you must use a form -- you should
never enter data directly into tables anyway...
If you specify details of what you are doing, we can help you more
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
This may seem simple but it's driving me crazy. I want to go thru a
table and if a yes/no field is yes, I want to shhot one form, and if it
is no, I want to shoot another form. I can't seem to get this done in a
query or macro.
Any ideas??
Wayne said:I basically use the yes/no fld to indicate which customer I'm currently
working on. It seems to process ok. I really only need to know if there is a
way within a query to display a form or not display a form. My coupon form
has has a record source for the query that has cirteria for [runtot] >=50.
If this is true, I want to display the form, if not, I want to display my
menu form. Currently, if I execute the coupon form and the cirteria is NOT
met, the coupon form is displayed with NO information. This is where I would
want it to go to the main menu and not display the coupon form. Thanks
again...
Hi Wayne,
I do not see why you need the yes/no field in your customers table...
when you DO print a coupon, how are you indicating that in the database?
Is there a certain code you use in the transactions table? If so, you can
count the transactions since the last transaction record with that code
... or, if they have not gotten a coupon, the number of transactions.
Then, when you process a coupon, it needs to be indicated so that the
count starts again.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne said:Thanks for response, I'll try to give more details.
Sandwich shop owner has a tbl of customers and sales transactions (one to
many) relationship. The owner wnats to record each transaction. With each
transaction is processed, a yes/no fld is set to yes on the cust tbl.
When transactions reach >=50.00 for a customer, the owner wants the
option to print or not print a 5.00 off next purcahse coupon. I have
everything seeming working fine until I get to the "print coupon" form. I
query the cust tbl and check the [runtot] fld for >=50.00. What I need to
happen is if [runtot] >=50.00 display the coupon form, if <50.00 return
to main menu.
I seem to be close, but being new I can't seem to solve this one form or
another problem.
Crystal, thanks for your help and undersatnding.
WWV
"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
Have a subform control that you change the SourceObject for... one
SourceObject is the first form and the other is ther second form
but this cannot be done from a table -- you must use a form -- you should
never enter data directly into tables anyway...
If you specify details of what you are doing, we can help you more
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
This may seem simple but it's driving me crazy. I want to go thru a
table and if a yes/no field is yes, I want to shhot one form, and if it
is no, I want to shoot another form. I can't seem to get this done in a
query or macro.
Any ideas??
Hi Wayne,
I will answer your question... in a moment
First, you do not need a special field to indicate wheich customer you
are working on ... I am going to give you a process for highlighting the
current record on a continuous form. while I realize that is not your
goal, you can see how to place the current unique record ID on the form.
If you are wanting to pull up the last record you were creating or
modifying, make these fields in your table:
DateCreate, date, DefaultValue--> =Now()
DateUpdate, date
I will also give you code to place the record pointer when you open the
form
~~~
here is your answer
when you switch subforms, in the code behind your main form:
if me.subform_controlname.form.recordset.recordcount = 0 then
me.close acform, me.name, acSaveNo
end if
~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
make the following textbox controls in the detail section of your form:
name --> Me.RecordID_current
visible --> no
name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false
send this control to the back*
in the design view of the form, select HighlightBox
you may need to use the object drop-down -- 1st icon on the formatting
toolbar since it is behind everything)
conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW
or whatever color you want for your highlight
If my detail section background is White, I like to use light yellow for
a highlight
WHERE
RecordID is the name of your ID field control
then, in the form OnCurrent event, assign a value to the unbound
RecordID_current being unbound, it will have the same value on every record
'~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~
'============================
PLACE RECORD ON LAST ONE CREATED/UPDATED
'~~~~~~~~~~~~~~
Private Sub Form_Load()
On Error GoTo Form_Load_error
Dim mRecordID As Long, mDate1 As Date, mDate2
mDate1 = DMax("DateModified", "Employees")
mDate2 = DMax("DateCreated", "Employees")
If mDate2 > mDate1 Then
mRecordID = DLookup("IDfield", _
"Employees", _
"DateCreated=#" & mDate2 & "#")
Else
mRecordID = DLookup("IDfield", "Employees", _
"DateModified=#" & mDate1 & "#")
End If
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If
Exit Sub
Form_Load_error:
msgbox Err.Description, , "ERROR "
& Err.Number _
& " Emp FormLoad"
'press F8 to step thru code and fix problem
Stop
Resume
End Sub
'~~~~~~~~~~~~~~
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne said:I basically use the yes/no fld to indicate which customer I'm
currently working on. It seems to process ok. I really only need to
know if there is a way within a query to display a form or not display
a form. My coupon form has has a record source for the query that has
cirteria for [runtot] >=50. If this is true, I want to display the
form, if not, I want to display my menu form. Currently, if I execute
the coupon form and the cirteria is NOT met, the coupon form is
displayed with NO information. This is where I would want it to go to
the main menu and not display the coupon form. Thanks again...
Hi Wayne,
I do not see why you need the yes/no field in your customers table...
when you DO print a coupon, how are you indicating that in the
database? Is there a certain code you use in the transactions table?
If so, you can count the transactions since the last transaction
record with that code ... or, if they have not gotten a coupon, the
number of transactions. Then, when you process a coupon, it needs to
be indicated so that the count starts again.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
Thanks for response, I'll try to give more details.
Sandwich shop owner has a tbl of customers and sales transactions
(one to many) relationship. The owner wnats to record each
transaction. With each transaction is processed, a yes/no fld is set
to yes on the cust tbl. When transactions reach >=50.00 for a
customer, the owner wants the option to print or not print a 5.00
off next purcahse coupon. I have everything seeming working fine
until I get to the "print coupon" form. I query the cust tbl and
check the [runtot] fld for >=50.00. What I need to happen is if
[runtot] >=50.00 display the coupon form, if <50.00 return to main
menu.
I seem to be close, but being new I can't seem to solve this one
form or another problem.
Crystal, thanks for your help and undersatnding.
WWV
"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in
message
Have a subform control that you change the SourceObject for... one
SourceObject is the first form and the other is ther second form
but this cannot be done from a table -- you must use a form -- you
should never enter data directly into tables anyway...
If you specify details of what you are doing, we can help you more
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
This may seem simple but it's driving me crazy. I want to go thru
a table and if a yes/no field is yes, I want to shhot one form,
and if it is no, I want to shoot another form. I can't seem to get
this done in a query or macro.
Any ideas??
strive4peace said:ps
in the code I gave you, I used DateCreated and DateModified instead of
DateCreate and DateUpdate (I just recently decided to change my "standard"
fieldnames to be shorter while my stored examples still use the longer
names)
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Hi Wayne,
I will answer your question... in a moment
First, you do not need a special field to indicate wheich customer you
are working on ... I am going to give you a process for highlighting the
current record on a continuous form. while I realize that is not your
goal, you can see how to place the current unique record ID on the form.
If you are wanting to pull up the last record you were creating or
modifying, make these fields in your table:
DateCreate, date, DefaultValue--> =Now()
DateUpdate, date
I will also give you code to place the record pointer when you open the
form
~~~
here is your answer
when you switch subforms, in the code behind your main form:
if me.subform_controlname.form.recordset.recordcount = 0 then
me.close acform, me.name, acSaveNo
end if
~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
make the following textbox controls in the detail section of your form:
name --> Me.RecordID_current
visible --> no
name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false
send this control to the back*
in the design view of the form, select HighlightBox
you may need to use the object drop-down -- 1st icon on the formatting
toolbar since it is behind everything)
conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW
or whatever color you want for your highlight
If my detail section background is White, I like to use light yellow for
a highlight
WHERE
RecordID is the name of your ID field control
then, in the form OnCurrent event, assign a value to the unbound
RecordID_current being unbound, it will have the same value on every
record
'~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~
'============================
PLACE RECORD ON LAST ONE CREATED/UPDATED
'~~~~~~~~~~~~~~
Private Sub Form_Load()
On Error GoTo Form_Load_error
Dim mRecordID As Long, mDate1 As Date, mDate2
mDate1 = DMax("DateModified", "Employees")
mDate2 = DMax("DateCreated", "Employees")
If mDate2 > mDate1 Then
mRecordID = DLookup("IDfield", _
"Employees", _
"DateCreated=#" & mDate2 & "#")
Else
mRecordID = DLookup("IDfield", "Employees", _
"DateModified=#" & mDate1 & "#")
End If
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If
Exit Sub
Form_Load_error:
msgbox Err.Description, , "ERROR "
& Err.Number _
& " Emp FormLoad"
'press F8 to step thru code and fix problem
Stop
Resume
End Sub
'~~~~~~~~~~~~~~
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne said:I basically use the yes/no fld to indicate which customer I'm currently
working on. It seems to process ok. I really only need to know if there
is a way within a query to display a form or not display a form. My
coupon form has has a record source for the query that has cirteria for
[runtot] >=50. If this is true, I want to display the form, if not, I
want to display my menu form. Currently, if I execute the coupon form
and the cirteria is NOT met, the coupon form is displayed with NO
information. This is where I would want it to go to the main menu and
not display the coupon form. Thanks again...
"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
Hi Wayne,
I do not see why you need the yes/no field in your customers table...
when you DO print a coupon, how are you indicating that in the
database? Is there a certain code you use in the transactions table?
If so, you can count the transactions since the last transaction record
with that code ... or, if they have not gotten a coupon, the number of
transactions. Then, when you process a coupon, it needs to be indicated
so that the count starts again.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
Thanks for response, I'll try to give more details.
Sandwich shop owner has a tbl of customers and sales transactions (one
to many) relationship. The owner wnats to record each transaction.
With each transaction is processed, a yes/no fld is set to yes on the
cust tbl. When transactions reach >=50.00 for a customer, the owner
wants the option to print or not print a 5.00 off next purcahse
coupon. I have everything seeming working fine until I get to the
"print coupon" form. I query the cust tbl and check the [runtot] fld
for >=50.00. What I need to happen is if [runtot] >=50.00 display the
coupon form, if <50.00 return to main menu.
I seem to be close, but being new I can't seem to solve this one form
or another problem.
Crystal, thanks for your help and undersatnding.
WWV
"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
Have a subform control that you change the SourceObject for... one
SourceObject is the first form and the other is ther second form
but this cannot be done from a table -- you must use a form -- you
should never enter data directly into tables anyway...
If you specify details of what you are doing, we can help you more
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
This may seem simple but it's driving me crazy. I want to go thru a
table and if a yes/no field is yes, I want to shhot one form, and
if it is no, I want to shoot another form. I can't seem to get this
done in a query or macro.
Any ideas??
Wayne said:Thanks Crystal. I'm still javing problems. I'm goin to try to restate my
prob with examples. Please help, I'm at my wits end....
WV
ps
in the code I gave you, I used DateCreated and DateModified instead of
DateCreate and DateUpdate (I just recently decided to change my "standard"
fieldnames to be shorter while my stored examples still use the longer
names)
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Hi Wayne,
I will answer your question... in a moment
First, you do not need a special field to indicate wheich customer you
are working on ... I am going to give you a process for highlighting the
current record on a continuous form. while I realize that is not your
goal, you can see how to place the current unique record ID on the form.
If you are wanting to pull up the last record you were creating or
modifying, make these fields in your table:
DateCreate, date, DefaultValue--> =Now()
DateUpdate, date
I will also give you code to place the record pointer when you open the
form
~~~
here is your answer
when you switch subforms, in the code behind your main form:
if me.subform_controlname.form.recordset.recordcount = 0 then
me.close acform, me.name, acSaveNo
end if
~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
make the following textbox controls in the detail section of your form:
name --> Me.RecordID_current
visible --> no
name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false
send this control to the back*
in the design view of the form, select HighlightBox
you may need to use the object drop-down -- 1st icon on the formatting
toolbar since it is behind everything)
conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW
or whatever color you want for your highlight
If my detail section background is White, I like to use light yellow for
a highlight
WHERE
RecordID is the name of your ID field control
then, in the form OnCurrent event, assign a value to the unbound
RecordID_current being unbound, it will have the same value on every
record
'~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~
'============================
PLACE RECORD ON LAST ONE CREATED/UPDATED
'~~~~~~~~~~~~~~
Private Sub Form_Load()
On Error GoTo Form_Load_error
Dim mRecordID As Long, mDate1 As Date, mDate2
mDate1 = DMax("DateModified", "Employees")
mDate2 = DMax("DateCreated", "Employees")
If mDate2 > mDate1 Then
mRecordID = DLookup("IDfield", _
"Employees", _
"DateCreated=#" & mDate2 & "#")
Else
mRecordID = DLookup("IDfield", "Employees", _
"DateModified=#" & mDate1 & "#")
End If
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If
Exit Sub
Form_Load_error:
msgbox Err.Description, , "ERROR "
& Err.Number _
& " Emp FormLoad"
'press F8 to step thru code and fix problem
Stop
Resume
End Sub
'~~~~~~~~~~~~~~
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
I basically use the yes/no fld to indicate which customer I'm currently
working on. It seems to process ok. I really only need to know if there
is a way within a query to display a form or not display a form. My
coupon form has has a record source for the query that has cirteria for
[runtot] >=50. If this is true, I want to display the form, if not, I
want to display my menu form. Currently, if I execute the coupon form
and the cirteria is NOT met, the coupon form is displayed with NO
information. This is where I would want it to go to the main menu and
not display the coupon form. Thanks again...
"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
Hi Wayne,
I do not see why you need the yes/no field in your customers table...
when you DO print a coupon, how are you indicating that in the
database? Is there a certain code you use in the transactions table?
If so, you can count the transactions since the last transaction record
with that code ... or, if they have not gotten a coupon, the number of
transactions. Then, when you process a coupon, it needs to be indicated
so that the count starts again.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
Thanks for response, I'll try to give more details.
Sandwich shop owner has a tbl of customers and sales transactions (one
to many) relationship. The owner wnats to record each transaction.
With each transaction is processed, a yes/no fld is set to yes on the
cust tbl. When transactions reach >=50.00 for a customer, the owner
wants the option to print or not print a 5.00 off next purcahse
coupon. I have everything seeming working fine until I get to the
"print coupon" form. I query the cust tbl and check the [runtot] fld
for >=50.00. What I need to happen is if [runtot] >=50.00 display the
coupon form, if <50.00 return to main menu.
I seem to be close, but being new I can't seem to solve this one form
or another problem.
Crystal, thanks for your help and undersatnding.
WWV
"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
Have a subform control that you change the SourceObject for... one
SourceObject is the first form and the other is ther second form
but this cannot be done from a table -- you must use a form -- you
should never enter data directly into tables anyway...
If you specify details of what you are doing, we can help you more
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
Wayne Veale wrote:
This may seem simple but it's driving me crazy. I want to go thru a
table and if a yes/no field is yes, I want to shhot one form, and
if it is no, I want to shoot another form. I can't seem to get this
done in a query or macro.
Any ideas??