3 linked combo boxes in a form Options

[Opal]

I'm sorry if I'm confusing you.
I need to learn to type, or find a cure for dislexia.

Check the line
Dim cltx as controls. it should be
Dim cltx as controls
and the next lines have a me.ctl.something.
delete the me. from those lines

Uh....I'm sorry....still confused.....the code you gave me is:

Dim cltx As Controls
Dim ctl As Control
Set ctlx = Me.Controls
For Each ctl In ctlx
If Me.ctl.Tag = "Clear Me" Then
Me.ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing


You just wrote:

Dim cltx as controls. it should be
Dim cltx as controls

aren't these two lines the same?

You say to remove me from some lines, should it read:

Dim cltx As Controls
Dim ctl As Control
Set ctlx = .Controls
For Each ctl In ctlx
If .ctl.Tag = "Clear Me" Then
.ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

 

[Bob Quintal]

--

Uh....I'm sorry....still confused.....the code you gave me is:

Dim cltx As Controls

that should be c t l x. to match the set line below.

Dim ctl As Control
Set ctlx = Me.Controls
For Each ctl In ctlx
If Me.ctl.Tag = "Clear Me" Then
Me.ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing


You just wrote:

Dim cltx as controls. it should be
Dim cltx as controls



aren't these two lines the same?

yeah, but they shoudln't be

You say to remove me from some lines, should it read:

Dim cltx As Controls
Dim ctl As Control
Set ctlx = .Controls
For Each ctl In ctlx
If .ctl.Tag = "Clear Me" Then
.ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

not quite. you removed the me you also need to remove the period.
that was between me and ctl.

 

[Opal]

that should be c t l x. to match the set line below.









yeah, but they shoudln't be



not quite. you removed the me you also need to remove the period.
that was between me and ctl.

Hi Bob,

Okay....I missed the dyslexia last night....didn't see it until
now....but I am still having problems. If I use:

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Me.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

I get an invalid use of me Keyword.

If I use:

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

I get a compile error.....

 

[Bob Quintal]

Hi Bob,

Okay....I missed the dyslexia last night....didn't see it until
now....but I am still having problems. If I use:

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Me.Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

I get an invalid use of me Keyword.

try replacing the Me.Controls with
Forms!<<FormName>>.controls

If I use:

Dim ctlx As Controls
Dim ctl As Control
Set ctlx = Controls
For Each ctl In ctlx
If ctl.Tag = "Clear Me" Then
ctl.Value = ""
End If
Next ctl
Set ctlx = Nothing

I get a compile error.....

 

[Bob Quintal]

Okay...tried that....then input data into the form and get a:

"you can't assign a value to this object" error and the Date text
box gets cleared only.....

So I did some research and discovered that I probably should not
have named my text box "Date" as it is a reserved word in
Access....so I changed it everywhere to "txtDate" and I got the
same error.....

Open the form's code module, goto the line that starts
for each ctl
add a new line underneath that says
debug.print ctl.name
While the cursor is in that line, press the F9 function key.
Go to the form, go into normal view, enter data and press your clear
button.
when the code gets to that line, it will stop, with the line
highlighted, usually in yellow. Press F8 to ececue that line. If the
immediate window is open, you will see the name of the control in
it. If the immediate window is not open, pressing control-G will
open it, so you can see the name.

Press the F8 key repeatedly, to step through the code one line at a
time. until you find which control doesn't like being set to "". It
may be a numeric field, or a checkbox.

Once we know what it is, we can figure out how to deal with it.

 

[Opal]

try replacing the Me.Controls with
Forms!<<FormName>>.controls

Okay...tried that....then input data into the form and get a:

"you can't assign a value to this object" error and the Date text box
gets cleared only.....

So I did some research and discovered that I probably should not have
named my text box "Date" as it is a reserved word in Access....so I
changed it everywhere to "txtDate" and I got the same error.....

 

[Opal]

Okay...tried that....then input data into the form and get a:

"you can't assign a value to this object" error and the Date text box
gets cleared only.....

So I did some research and discovered that I probably should not have
named my text box "Date" as it is a reserved word in Access....so I
changed it everywhere to "txtDate" and I got the same error.....- Hide quoted text -

- Show quoted text -

All the data goes to the table, but only the txtDate,
txtRequestorName, txtDescription and txtAdditionalComments boxes are
cleared after the submit button is clicked. I still get the "you
can't assign a value to this object" error as well. All combo boxes
remain populated with data.

 

[Opal]

Open the form's code module, goto the line that starts
for each ctl
add a new line underneath that says
debug.print ctl.name
While the cursor is in that line, press the F9 function key.
Go to the form, go into normal view, enter data and press your clear
button.
when the code gets to that line, it will stop, with the line
highlighted, usually in yellow. Press F8 to ececue that line. If the
immediate window is open, you will see the name of the control in
it. If the immediate window is not open, pressing control-G will
open it, so you can see the name.

Press the F8 key repeatedly, to step through the code one line at a
time. until you find which control doesn't like being set to "". It
may be a numeric field, or a checkbox.

Once we know what it is, we can figure out how to deal with it.

Thanks, Bob, I will try this. I didn't see this post when I made my
last post.

 

[Opal]

Open the form's code module, goto the line that starts
for each ctl
add a new line underneath that says
debug.print ctl.name
While the cursor is in that line, press the F9 function key.
Go to the form, go into normal view, enter data and press your clear
button.
when the code gets to that line, it will stop, with the line
highlighted, usually in yellow. Press F8 to ececue that line. If the
immediate window is open, you will see the name of the control in
it. If the immediate window is not open, pressing control-G will
open it, so you can see the name.

Press the F8 key repeatedly, to step through the code one line at a
time. until you find which control doesn't like being set to "". It
may be a numeric field, or a checkbox.

Once we know what it is, we can figure out how to deal with it.

Found the problem, the text box that will not accept the "Clear Me"
option is the MachineName box. This is the box that is loaded from a
subform which runs a query off the MachineNumber combo box to load
this text box. So I guess I need to clear this text independant of
this code and figure out how to write the "If Me.NewRecord...." for
the DMax auto number function.

I removed the "Clear Me" tag from the MachineName text box and the
rest of the form clears as it should.

 

[Bob Quintal]

Found the problem, the text box that will not accept the "Clear
Me" option is the MachineName box. This is the box that is loaded
from a subform which runs a query off the MachineNumber combo box
to load this text box. So I guess I need to clear this text
independant of this code and figure out how to write the "If
Me.NewRecord...." for the DMax auto number function.

I removed the "Clear Me" tag from the MachineName text box and the
rest of the form clears as it should.

Congratulations, you're making progress.

Do you need to clear this combobox? I think that if you simply clear
the combobox, that it would clear this calculated textbox too.

As to the
If Me.newrecord then
me.txtAutonumber = nz(Dmax("field","table"),0)+1
end if
is the template you should use. Change the field and table entries
to the real names, and that should work.

 

[Opal]

Congratulations, you're making progress.

Do you need to clear this combobox? I think that if you simply clear
the combobox, that it would clear this calculated textbox too.

As to the
If Me.newrecord then
me.txtAutonumber = nz(Dmax("field","table"),0)+1
end if
is the template you should use. Change the field and table entries
to the real names, and that should work.

--
Bob Quintal

PA is y I've altered my email address.

--
- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thank you Bob....where would this template go? It doesn't work in the
forms "on Current" event where I have the

Me.KaizenNumber = Nz(DMax("KaizenNumber", "KaizenTrackingtbl"), 0) + 1

Which was the original code used to create the auto-number. If I turn
this line "off" then I do not get an auto number at all when I open
the form.

As for the text box, yes I do need to clear it. The combo box that
the subform feeds off of clears just fine. So I went to the sub form
and tried to tag it with the "Clear Me" but the text box remained
populated....Not sure where to go from here....

 

[Bob Quintal]

Thank you Bob....where would this template go? It doesn't work in
the forms "on Current" event where I have the

Me.KaizenNumber = Nz(DMax("KaizenNumber", "KaizenTrackingtbl"), 0)
+ 1

Which was the original code used to create the auto-number. If I
turn this line "off" then I do not get an auto number at all when
I open the form.

It dawned on me that you don't need the IF me.newrecord because your
form is unbound, if I recall correctly.

As for the text box, yes I do need to clear it. The combo box
that the subform feeds off of clears just fine. So I went to the
sub form and tried to tag it with the "Clear Me" but the text box
remained populated....Not sure where to go from here....

No, you can't clear a subform. Perhaps a me.combobox.requery or a
subform.requery would work to clear the textbox..

 

[Bob Quintal]

in














It dawned on me that you don't need the IF me.newrecord because
your form is unbound, if I recall correctly.




No, you can't clear a subform. Perhaps a me.combobox.requery or a
subform.requery would work to clear the textbox..



--
Bob Quintal

PA is y I've altered my email address.

--
-
Hide quoted text -

- Show quoted text -

Yes the form is unbound, but the DMax() function generates a new
number on current, but not after I submit. How can I get it to
generate a new number after submit? Should I place it in the
after update event as well?

As for the the text box that feeds off the Equipment subform,
there is a subform requery after update on the combo box:

Private Sub MachineNumber_AfterUpdate()
Forms![KaizenTrackingfrm].[Equipmentsubform].Requery
End Sub

I will keep researching........

Yes, place it in the code that does the write of the data, as part
of the clearing routine.

You could try adding the requery line there too, as an afterUpdate
event does not get triggered when you change the combobox using
code.

Q

 

[Opal]

It dawned on me that you don't need the IF me.newrecord because your
form is unbound, if I recall correctly.




No, you can't clear a subform. Perhaps a me.combobox.requery or a
subform.requery would work to clear the textbox..

Yes the form is unbound, but the DMax() function generates a new
number on current, but not after I submit. How can I get it to
generate a new number after submit? Should I place it in the after
update event as well?

As for the the text box that feeds off the Equipment subform, there is
a subform requery after update on the combo box:

Private Sub MachineNumber_AfterUpdate()
Forms![KaizenTrackingfrm].[Equipmentsubform].Requery
End Sub

I will keep researching........

 

[Bob Quintal]

in

innews:[email protected]:

Posted via a free Usenet account
fromhttp://www.teranews.com-Hide quoted text -

- Show quoted text -

Found the problem, the text box that will not accept the
"Clear Me" option is the MachineName box. This is the
box that is loaded from a subform which runs a query off
the MachineNumber combo box to load this text box. So I
guess I need to clear this text independant of this code
and figure out how to write the "If Me.NewRecord...." for
the DMax auto number function.

I removed the "Clear Me" tag from the MachineName text
box and the rest of the form clears as it should.

Congratulations, you're making progress.

Do you need to clear this combobox? I think that if you
simply clear the combobox, that it would clear this
calculated textbox too.

As to the
If Me.newrecord then
me.txtAutonumber = nz(Dmax("field","table"),0)+1
end if
is the template you should use. Change the field and table
entries to the real names, and that should work.

PA is y I've altered my email address.

Me.KaizenNumber = Nz(DMax("KaizenNumber",
"KaizenTrackingtbl"), 0) + 1

Which was the original code used to create the auto-number.
If I turn this line "off" then I do not get an auto number
at all when I open the form.

It dawned on me that you don't need the IF me.newrecord
because your form is unbound, if I recall correctly.

As for the text box, yes I do need to clear it. The combo
box that the subform feeds off of clears just fine. So I
went to the sub form and tried to tag it with the "Clear Me"
but the text box remained populated....Not sure where to go
from here....

No, you can't clear a subform. Perhaps a me.combobox.requery
or a subform.requery would work to clear the textbox..

PA is y I've altered my email address.

- Show quoted text -

Yes the form is unbound, but the DMax() function generates a
new number on current, but not after I submit. How can I get
it to generate a new number after submit? Should I place it in
the after update event as well?

As for the the text box that feeds off the Equipment subform,
there is a subform requery after update on the combo box:

Private Sub MachineNumber_AfterUpdate()
Forms![KaizenTrackingfrm].[Equipmentsubform].Requery
End Sub

I will keep researching........

Yes, place it in the code that does the write of the data, as
part of the clearing routine.

You could try adding the requery line there too, as an
afterUpdate event does not get triggered when you change the
combobox using code.

Q

--
Bob Quintal

PA is y I've altered my email address.

--
-
Hide quoted text -

- Show quoted text -

AHA .,....I put the:

Forms![KaizenTrackingfrm].[Equipmentsubform].Requery

in the module and it works! YES!!!

Now....to get it to continue to auto number.......

just add the dmax statement under the requery.

 

[Opal]

Yes the form is unbound, but the DMax() function generates a new
number on current, but not after I submit. How can I get it to
generate a new number after submit? Should I place it in the
after update event as well?

As for the the text box that feeds off the Equipment subform,
there is a subform requery after update on the combo box:

Private Sub MachineNumber_AfterUpdate()
Forms![KaizenTrackingfrm].[Equipmentsubform].Requery
End Sub

I will keep researching........

Yes, place it in the code that does the write of the data, as part
of the clearing routine.

You could try adding the requery line there too, as an afterUpdate
event does not get triggered when you change the combobox using
code.

Q

AHA .,....I put the:

Forms![KaizenTrackingfrm].[Equipmentsubform].Requery

in the module and it works! YES!!!

Now....to get it to continue to auto number.......

 

[Bob Quintal]

in

innews:[email protected]:

On Sep 1, 5:11 am, Bob Quintal <[email protected]>
wrote:
innews:1188610347.576390.86170

@y42g2000hsy.googlegroups.com:

Posted via a free Usenet account
fromhttp://www.teranews.com-Hidequoted text -

- Show quoted text -

Found the problem, the text box that will not accept
the "Clear Me" option is the MachineName box. This is
the box that is loaded from a subform which runs a
query off the MachineNumber combo box to load this
text box. So I guess I need to clear this text
independant of this code and figure out how to write
the "If Me.NewRecord...." for the DMax auto number
function.

I removed the "Clear Me" tag from the MachineName text
box and the rest of the form clears as it should.

Congratulations, you're making progress.

Do you need to clear this combobox? I think that if you
simply clear the combobox, that it would clear this
calculated textbox too.

As to the
If Me.newrecord then
me.txtAutonumber = nz(Dmax("field","table"),0)+1
end if
is the template you should use. Change the field and
table entries to the real names, and that should work.

PA is y I've altered my email address.

Me.KaizenNumber = Nz(DMax("KaizenNumber",
"KaizenTrackingtbl"), 0) + 1

Which was the original code used to create the
auto-number. If I turn this line "off" then I do not get
an auto number at all when I open the form.

It dawned on me that you don't need the IF me.newrecord
because your form is unbound, if I recall correctly.

As for the text box, yes I do need to clear it. The
combo box that the subform feeds off of clears just fine.
So I went to the sub form and tried to tag it with the
"Clear Me" but the text box remained populated....Not
sure where to go from here....

No, you can't clear a subform. Perhaps a
me.combobox.requery or a subform.requery would work to
clear the textbox..

PA is y I've altered my email address.

- Show quoted text -

Yes the form is unbound, but the DMax() function generates a
new number on current, but not after I submit. How can I
get it to generate a new number after submit? Should I
place it in the after update event as well?

As for the the text box that feeds off the Equipment
subform, there is a subform requery after update on the
combo box:

Private Sub MachineNumber_AfterUpdate()
Forms![KaizenTrackingfrm].[Equipmentsubform].Requery
End Sub

I will keep researching........

Yes, place it in the code that does the write of the data, as
part of the clearing routine.

You could try adding the requery line there too, as an
afterUpdate event does not get triggered when you change the
combobox using code.

PA is y I've altered my email address.

- Show quoted text -

AHA .,....I put the:

Forms![KaizenTrackingfrm].[Equipmentsubform].Requery

in the module and it works! YES!!!

Now....to get it to continue to auto number.......

just add the dmax statement under the requery.

--
Bob Quintal

PA is y I've altered my email address.

--
-
Hide quoted text -

- Show quoted text -

Alas, I thought of that, but as the DMax statement is:

Me.KaizenNumber = Nz(DMax("KaizenNumber", "KaizenTrackingtbl"), 0)
+ 1

and if you meant for me to but it after the requery in the module,
it won't work:

"Invalid use of Me"

So instead I put in:

Forms![KaizenTrackingfrm].[KaizenNumber] = Nz(DMax("KaizenNumber",
"KaizenTrackingtbl"), 0) + 1

AND IT WORKS BEAUTIFULLY!!!

Yeah!!! Thank you much for your help and support!

Now onto my next project. (inventory database)

Good luck. Now you know where to come for help.

 

[Opal]

in
in
Posted via a free Usenet account
fromhttp://www.teranews.com-Hidequoted text -
- Show quoted text -
Found the problem, the text box that will not accept the
"Clear Me" option is the MachineName box. This is the
box that is loaded from a subform which runs a query off
the MachineNumber combo box to load this text box. So I
guess I need to clear this text independant of this code
and figure out how to write the "If Me.NewRecord...." for
the DMax auto number function.
I removed the "Clear Me" tag from the MachineName text
box and the rest of the form clears as it should.
Congratulations, you're making progress.
Do you need to clear this combobox? I think that if you
simply clear the combobox, that it would clear this
calculated textbox too.
As to the
If Me.newrecord then
me.txtAutonumber = nz(Dmax("field","table"),0)+1
end if
is the template you should use. Change the field and table
entries to the real names, and that should work.
--
Bob Quintal
PA is y I've altered my email address.
--
Thank you Bob....where would this template go? It doesn't
work in the forms "on Current" event where I have the
Me.KaizenNumber = Nz(DMax("KaizenNumber",
"KaizenTrackingtbl"), 0) + 1
Which was the original code used to create the auto-number.
If I turn this line "off" then I do not get an auto number
at all when I open the form.
It dawned on me that you don't need the IF me.newrecord
because your form is unbound, if I recall correctly.
As for the text box, yes I do need to clear it. The combo
box that the subform feeds off of clears just fine. So I
went to the sub form and tried to tag it with the "Clear Me"
but the text box remained populated....Not sure where to go
from here....
No, you can't clear a subform. Perhaps a me.combobox.requery
or a subform.requery would work to clear the textbox..
--
Bob Quintal
PA is y I've altered my email address.
--
-
Hide quoted text -
- Show quoted text -
Yes the form is unbound, but the DMax() function generates a
new number on current, but not after I submit. How can I get
it to generate a new number after submit? Should I place it in
the after update event as well?
As for the the text box that feeds off the Equipment subform,
there is a subform requery after update on the combo box:
Private Sub MachineNumber_AfterUpdate()
Forms![KaizenTrackingfrm].[Equipmentsubform].Requery
End Sub
I will keep researching........
Yes, place it in the code that does the write of the data, as
part of the clearing routine.
You could try adding the requery line there too, as an
afterUpdate event does not get triggered when you change the
combobox using code.
Q

AHA .,....I put the:

Forms![KaizenTrackingfrm].[Equipmentsubform].Requery

in the module and it works! YES!!!

Now....to get it to continue to auto number.......

just add the dmax statement under the requery.

Alas, I thought of that, but as the DMax statement is:

Me.KaizenNumber = Nz(DMax("KaizenNumber", "KaizenTrackingtbl"), 0) + 1

and if you meant for me to but it after the requery in the module, it
won't work:

"Invalid use of Me"

So instead I put in:

Forms![KaizenTrackingfrm].[KaizenNumber] = Nz(DMax("KaizenNumber",
"KaizenTrackingtbl"), 0) + 1

AND IT WORKS BEAUTIFULLY!!!

Yeah!!! Thank you much for your help and support!

Now onto my next project. (inventory database)