3 linked combo boxes in a form Options

[Opal]

Thank you for your response. Unfortunately, the option you suggested
did not produce the desired result. Perhaps if I explain further
what
I am trying to do, you can offer some alternatives.

Shopcbo based on Shopqry based on Shoptbl. Columns in the table are
ShopName and ShopID. The shop same is self explanatory. The shop
ID
is a field a created to match up with a field in the equipment
table.


Areacbo based on Areaqry based on Areatbl. Columns in the table are
AreaName, AreaLink and ShopName. The area link is a field created to
identify each area that a piece of equipment belongs to and has a
corresponding field in the equipment table.


I have revised the form with VB code, as opposed to a macro, to run
the requery so that whenever the Shopcbo is changed, the Areacbo
"updates" with the area selections available to that specific shop.


Now the Equipmenttbl has 4 columns: EquipmentName, EquipmentNumber,
AreaLink, ShopID. The Equipment table was created from a make table
query because I linked 3 separate equipment tables from outher
databases to this database I am working in. Three shops maintain
separate databases with equipment listed for other purposes. Since I
did not want have to update a table in this database everytime a
piece
of equipment was added, or changed or deleted, I chose to link them
to
this database. I performed a union query to bring them all together
into one Equipmenttbl. As a result of the Equipmenttble data coming
from separate databases, the AreaLink number is not unique. For
example: Two shops have several areas in their shop named the same
and since they work out of two different databases, the AreaLink
number is also the same. That is why I added the ShopID indicated
to
differentiate between the two shops. So, even though the areas may
be
named and numbered the same, the equipment in these areas is
completely different. Follow me so far?


Shop Name Shop ID Area Name Area ID
EquipmentName EquipmentNumber
North A Subs
3 Robot ABC -1234
South B Subs
3 Snake Robot XYZ - 4576


I need to be able to have a combo box for the Equipment that will only
bring up a specific list of equipment for each shop and area. There
are over 900 pieces of equipment in the table, and I cannot bring up
all those in the combo box. However, there would only be 10 at most
per area.


I though perhaps a parameter query from the Equipment table that looks
for the specific ShopID and AreaID based on the results from the
Shopcbo and Areacbo selections....A SELECT .... WHERE statement
perhaps..... But I am just unclear as how to accomplish this.


Your assistance would be appreciated. Thank you.

 

[Bob Quintal]

Thank you for your response. Unfortunately, the option you
suggested did not produce the desired result. Perhaps if I
explain further what
I am trying to do, you can offer some alternatives.

Shopcbo based on Shopqry based on Shoptbl. Columns in the
table are ShopName and ShopID. The shop same is self
explanatory. The shop ID
is a field a created to match up with a field in the equipment
table.


Areacbo based on Areaqry based on Areatbl. Columns in the
table are AreaName, AreaLink and ShopName. The area link is a
field created to identify each area that a piece of equipment
belongs to and has a corresponding field in the equipment
table.


I have revised the form with VB code, as opposed to a macro,
to run the requery so that whenever the Shopcbo is changed,
the Areacbo "updates" with the area selections available to
that specific shop.


Now the Equipmenttbl has 4 columns: EquipmentName,
EquipmentNumber, AreaLink, ShopID. The Equipment table was
created from a make table query because I linked 3 separate
equipment tables from outher databases to this database I am
working in. Three shops maintain separate databases with
equipment listed for other purposes. Since I did not want
have to update a table in this database everytime a piece
of equipment was added, or changed or deleted, I chose to link
them to
this database. I performed a union query to bring them all
together into one Equipmenttbl. As a result of the
Equipmenttble data coming from separate databases, the
AreaLink number is not unique. For example: Two shops have
several areas in their shop named the same and since they work
out of two different databases, the AreaLink number is also
the same. That is why I added the ShopID indicated to
differentiate between the two shops. So, even though the
areas may be
named and numbered the same, the equipment in these areas is
completely different. Follow me so far?


Shop Name Shop ID Area Name Area ID
EquipmentName EquipmentNumber
North A Subs
3 Robot ABC -1234
South B Subs
3 Snake Robot XYZ - 4576


I need to be able to have a combo box for the Equipment that
will only bring up a specific list of equipment for each shop
and area. There are over 900 pieces of equipment in the
table, and I cannot bring up all those in the combo box.
However, there would only be 10 at most per area.


I though perhaps a parameter query from the Equipment table
that looks for the specific ShopID and AreaID based on the
results from the Shopcbo and Areacbo selections....A SELECT
.... WHERE statement perhaps..... But I am just unclear as
how to accomplish this.

If the bound colums in the combo boxes are the ID numbers.
this statement in the record source of the cboEquipment should
work.

SELECT EquipmentNumber, EquipmentName
FROM EquipmentTBL
WHERE [SHOP ID] = Forms![yourform]!cboShop
AND [arealink] = Forms!yourform!cboArea

If you have bound the comboboxes to the name instead, you could
try to set some hidden textboxes with the IDs in the afterUpdate
event of the respective control, and use those in the where
clause.

Your assistance would be appreciated. Thank you.

HTH

 

[Opal]

Thank you for your response. Unfortunately, the option you
suggested did not produce the desired result. Perhaps if I
explain further what
I am trying to do, you can offer some alternatives.

Shopcbo based on Shopqry based on Shoptbl. Columns in the
table are ShopName and ShopID. The shop same is self
explanatory. The shop ID
is a field a created to match up with a field in the equipment
table.

Areacbo based on Areaqry based on Areatbl. Columns in the
table are AreaName, AreaLink and ShopName. The area link is a
field created to identify each area that a piece of equipment
belongs to and has a corresponding field in the equipment
table.

I have revised the form with VB code, as opposed to a macro,
to run the requery so that whenever the Shopcbo is changed,
the Areacbo "updates" with the area selections available to
that specific shop.

Now the Equipmenttbl has 4 columns: EquipmentName,
EquipmentNumber, AreaLink, ShopID. The Equipment table was
created from a make table query because I linked 3 separate
equipment tables from outher databases to this database I am
working in. Three shops maintain separate databases with
equipment listed for other purposes. Since I did not want
have to update a table in this database everytime a piece
of equipment was added, or changed or deleted, I chose to link
them to
this database. I performed a union query to bring them all
together into one Equipmenttbl. As a result of the
Equipmenttble data coming from separate databases, the
AreaLink number is not unique. For example: Two shops have
several areas in their shop named the same and since they work
out of two different databases, the AreaLink number is also
the same. That is why I added the ShopID indicated to
differentiate between the two shops. So, even though the
areas may be
named and numbered the same, the equipment in these areas is
completely different. Follow me so far?

Shop Name Shop ID Area Name Area ID
EquipmentName EquipmentNumber
North A Subs
3 Robot ABC -1234
South B Subs
3 Snake Robot XYZ - 4576

I need to be able to have a combo box for the Equipment that
will only bring up a specific list of equipment for each shop
and area. There are over 900 pieces of equipment in the
table, and I cannot bring up all those in the combo box.
However, there would only be 10 at most per area.

I though perhaps a parameter query from the Equipment table
that looks for the specific ShopID and AreaID based on the
results from the Shopcbo and Areacbo selections....A SELECT
.... WHERE statement perhaps..... But I am just unclear as
how to accomplish this.

If the bound colums in the combo boxes are the ID numbers.
this statement in the record source of the cboEquipment should
work.

SELECT EquipmentNumber, EquipmentName
FROM EquipmentTBL
WHERE [SHOP ID] = Forms![yourform]!cboShop
AND [arealink] = Forms!yourform!cboArea

If you have bound the comboboxes to the name instead, you could
try to set some hidden textboxes with the IDs in the afterUpdate
event of the respective control, and use those in the where
clause.

Your assistance would be appreciated. Thank you.

HTH

Thank you....I will give this a try and let you know.

 

[Opal]

Thank you for your response. Unfortunately, the option you
suggested did not produce the desired result. Perhaps if I
explain further what
I am trying to do, you can offer some alternatives.

Shopcbo based on Shopqry based on Shoptbl. Columns in the
table are ShopName and ShopID. The shop same is self
explanatory. The shop ID
is a field a created to match up with a field in the equipment
table.

Areacbo based on Areaqry based on Areatbl. Columns in the
table are AreaName, AreaLink and ShopName. The area link is a
field created to identify each area that a piece of equipment
belongs to and has a corresponding field in the equipment
table.

I have revised the form with VB code, as opposed to a macro,
to run the requery so that whenever the Shopcbo is changed,
the Areacbo "updates" with the area selections available to
that specific shop.

Now the Equipmenttbl has 4 columns: EquipmentName,
EquipmentNumber, AreaLink, ShopID. The Equipment table was
created from a make table query because I linked 3 separate
equipment tables from outher databases to this database I am
working in. Three shops maintain separate databases with
equipment listed for other purposes. Since I did not want
have to update a table in this database everytime a piece
of equipment was added, or changed or deleted, I chose to link
them to
this database. I performed a union query to bring them all
together into one Equipmenttbl. As a result of the
Equipmenttble data coming from separate databases, the
AreaLink number is not unique. For example: Two shops have
several areas in their shop named the same and since they work
out of two different databases, the AreaLink number is also
the same. That is why I added the ShopID indicated to
differentiate between the two shops. So, even though the
areas may be
named and numbered the same, the equipment in these areas is
completely different. Follow me so far?

Shop Name Shop ID Area Name Area ID
EquipmentName EquipmentNumber
North A Subs
3 Robot ABC -1234
South B Subs
3 Snake Robot XYZ - 4576

I need to be able to have a combo box for the Equipment that
will only bring up a specific list of equipment for each shop
and area. There are over 900 pieces of equipment in the
table, and I cannot bring up all those in the combo box.
However, there would only be 10 at most per area.

I though perhaps a parameter query from the Equipment table
that looks for the specific ShopID and AreaID based on the
results from the Shopcbo and Areacbo selections....A SELECT
.... WHERE statement perhaps..... But I am just unclear as
how to accomplish this.

If the bound colums in the combo boxes are the ID numbers.
this statement in the record source of the cboEquipment should
work.

SELECT EquipmentNumber, EquipmentName
FROM EquipmentTBL
WHERE [SHOP ID] = Forms![yourform]!cboShop
AND [arealink] = Forms!yourform!cboArea

If you have bound the comboboxes to the name instead, you could
try to set some hidden textboxes with the IDs in the afterUpdate
event of the respective control, and use those in the where
clause.

Your assistance would be appreciated. Thank you.

HTH

Thank you, Bob... It worked really well. I had to use the hidden
text boxes, as suggest, but no my only problem is where to put the
requery... any suggestions?

 

[Bob Quintal]

in

Thank you for your response. Unfortunately, the option you
suggested did not produce the desired result. Perhaps if I
explain further what
I am trying to do, you can offer some alternatives.

Shopcbo based on Shopqry based on Shoptbl. Columns in the
table are ShopName and ShopID. The shop same is self
explanatory. The shop ID
is a field a created to match up with a field in the equipment
table.

Areacbo based on Areaqry based on Areatbl. Columns in the
table are AreaName, AreaLink and ShopName. The area link is a
field created to identify each area that a piece of equipment
belongs to and has a corresponding field in the equipment
table.

I have revised the form with VB code, as opposed to a macro,
to run the requery so that whenever the Shopcbo is changed,
the Areacbo "updates" with the area selections available to
that specific shop.

Now the Equipmenttbl has 4 columns: EquipmentName,
EquipmentNumber, AreaLink, ShopID. The Equipment table was
created from a make table query because I linked 3 separate
equipment tables from outher databases to this database I am
working in. Three shops maintain separate databases with
equipment listed for other purposes. Since I did not want
have to update a table in this database everytime a piece
of equipment was added, or changed or deleted, I chose to link
them to
this database. I performed a union query to bring them all
together into one Equipmenttbl. As a result of the
Equipmenttble data coming from separate databases, the
AreaLink number is not unique. For example: Two shops have
several areas in their shop named the same and since they work
out of two different databases, the AreaLink number is also
the same. That is why I added the ShopID indicated to
differentiate between the two shops. So, even though the
areas may be
named and numbered the same, the equipment in these areas is
completely different. Follow me so far?

Shop Name Shop ID Area Name Area ID
EquipmentName EquipmentNumber
North A Subs
3 Robot ABC -1234
South B Subs
3 Snake Robot XYZ - 4576

I need to be able to have a combo box for the Equipment that
will only bring up a specific list of equipment for each shop
and area. There are over 900 pieces of equipment in the
table, and I cannot bring up all those in the combo box.
However, there would only be 10 at most per area.

I though perhaps a parameter query from the Equipment table
that looks for the specific ShopID and AreaID based on the
results from the Shopcbo and Areacbo selections....A SELECT
.... WHERE statement perhaps..... But I am just unclear as
how to accomplish this.

If the bound colums in the combo boxes are the ID numbers.
this statement in the record source of the cboEquipment should
work.

SELECT EquipmentNumber, EquipmentName
FROM EquipmentTBL
WHERE [SHOP ID] = Forms![yourform]!cboShop
AND [arealink] = Forms!yourform!cboArea

If you have bound the comboboxes to the name instead, you could
try to set some hidden textboxes with the IDs in the afterUpdate
event of the respective control, and use those in the where
clause.

Your assistance would be appreciated. Thank you.

HTH

--
Bob Quintal

PA is y I've altered my email address.

--
-
Hide quoted text -

- Show quoted text -

Thank you, Bob... It worked really well. I had to use the hidden
text boxes, as suggest, but no my only problem is where to put the
requery... any suggestions?

Your areacbo needs to be requeried after a change in shopcbo
(shopcbo AfterUpdate event) or a change in the record (On Current
event). So put the Me.areacbo.requery in both places.

The equipmentcbo needs to be requeried when the areacbo changes
(cboArea After Update event), or when the shopcbo changes
(shopcbo.AfterUpdate event, or when moving to a different record (On
Current Event) so put me.equipmentcbo.requery in all three places.

Q

 

[Opal]

Thank you, Bob....I was missing the "on current event" Had it for the
Shopcbo, but missed it on the equipmentcbo and was going crazy with
different combinations to try and get it to work. Can you help with
"Nulls"?

I have the following code for the Submit function:

Private Sub CmdSubmit_Click()
If IsNull(Date) Then
MsgBox "You must select a Date."
Exit Sub
End If
If IsNull(KPI) Then
MsgBox "You must select a KPI."
Exit Sub
End If
If IsNull(Shopcbo) Then
MsgBox "You must select a Shop."
Exit Sub
End If
If IsNull(Areacbo) Then
MsgBox "You must select an Area or 'Other'."
Exit Sub
End If
If IsNull(Description) Then
MsgBox "You must enter a description of the Problem or
Kaizen."
Exit Sub
End If
If IsNull(Status) Then
MsgBox "You must select a Status Option."
Exit Sub
End If
If IsNull(Priority) Then
MsgBox "You must select a Priority Value."
Exit Sub
End If
SubmitKaizen
End Sub

from this code, I want to go to the following module to finish sending
the data collected in the form to the table:

Sub SubmitKaizen()
On Error GoTo Err_Submit_Click
Dim HoldKaizenNumber As Integer
Dim HoldDate As Date
Dim HoldKPI As String
Dim HoldShopName As String
Dim HoldAreaName As String
Dim HoldMachineNumber As String
Dim HoldMachineName As String
Dim HoldRequestingTM As String
Dim HoldShift As String
Dim HoldDescription As String
Dim HoldPictureFile As String
Dim HoldAdditionalComments As String
Dim HoldStatus As String
Dim HoldPriority As String

Dim dbobject As DAO.Database
Dim KaizenRS As DAO.Recordset
Dim strquery As String

HoldShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
HoldAreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
HoldMachineNumber = Forms![KaizenTrackingfrm]!MachineNumber.Value
HoldMachineName = Forms![KaizenTrackingfrm]!MachineName.Value

Set dbobject = CurrentDb
strquery = "SELECT * FROM KaizenTrackingtbl;"
Set KaizenRS = dbobject.OpenRecordset(strquery)

DoCmd.GoToRecord , , acNewRec
Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub

However, when I click the "Submit" button I get an "invalid use of
Nulls" error. I have been reading through other posts on this error,
and wonder if I am missing an "Else if" statement ....but I'm just not
sure where to go with this. I'm still on a big learning curve and
would appreciate whatever help you can offer. Thanks!

 

[Bob Quintal]

Thank you, Bob....I was missing the "on current event" Had it for
the Shopcbo, but missed it on the equipmentcbo and was going crazy
with different combinations to try and get it to work. Can you
help with "Nulls"?

The issue is actually with how you are trying to add to the
recordset in submitkaizen. See my comments.

I have the following code for the Submit function:
[code snipped, it's fine]

from this code, I want to go to the following module to finish
sending the data collected in the form to the table:

Sub SubmitKaizen()
On Error GoTo Err_Submit_Click

these dims are Muda, you can refer directly to the controls when you
set the data into the hew record.

Dim HoldKaizenNumber As Integer
Dim HoldDate As Date
Dim HoldKPI As String
Dim HoldShopName As String
Dim HoldAreaName As String
Dim HoldMachineNumber As String
Dim HoldMachineName As String
Dim HoldRequestingTM As String
Dim HoldShift As String
Dim HoldDescription As String
Dim HoldPictureFile As String
Dim HoldAdditionalComments As String
Dim HoldStatus As String
Dim HoldPriority As String

Dim dbobject As DAO.Database
Dim KaizenRS As DAO.Recordset
Dim strquery As String

HoldShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
HoldAreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
HoldMachineNumber = Forms![KaizenTrackingfrm]!MachineNumber.Value
HoldMachineName = Forms![KaizenTrackingfrm]!MachineName.Value

Set dbobject = CurrentDb
strquery = "SELECT * FROM KaizenTrackingtbl;"
Set KaizenRS = dbobject.OpenRecordset(strquery)

Here is Mura
Docmd.gotorecord affects the record in the form, not the one in
therecordset.

DoCmd.GoToRecord , , acNewRec

To add the records to the set instead

KaizenRS.addnew
KaizenRS!Shopname = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
..... rtc.
KaizenRS.update

You can even simplify that by stating
With KaizenRS
.addnew
!Shopname = Forms![KaizenTrackingfrm]!Shopcbo.Value
!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
..... etc.
.update
End With

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub

However, when I click the "Submit" button I get an "invalid use of
Nulls" error. I have been reading through other posts on this
error, and wonder if I am missing an "Else if" statement ....but
I'm just not sure where to go with this. I'm still on a big
learning curve and would appreciate whatever help you can offer.
Thanks!

Your first procedure does not need an Else branch.

 

[Bob Quintal]

My original reply has not appeared on my newsserver yet, so here is
a new reply.

Thank you, Bob....I was missing the "on current event" Had it for
the Shopcbo, but missed it on the equipmentcbo and was going crazy
with different combinations to try and get it to work. Can you
help with "Nulls"?

Yes, but I don't think nulls are the problem.An empty string, "",
looks just like a null, but behaves differently.

You may want to test for empty strings too, but lets leave
discussion of that issue for later.

Just one thing, beware of using Access reserved words, date is one
of them, as field or variable names.

I have the following code for the Submit function:

Private Sub CmdSubmit_Click()
If IsNull(Date) Then
MsgBox "You must select a Date."
Exit Sub
End If

[ snip of 6 additional If statements, which look ok.

SubmitKaizen
End Sub

from this code, I want to go to the following module to finish
sending the data collected in the form to the table:

Here lie some problems, and some muda, muta and mari.

Sub SubmitKaizen()
On Error GoTo Err_Submit_Click

This first batch of variable assignments is not necessary, you can
set the field values directly from the form controls.

Dim HoldKaizenNumber As Integer
Dim HoldDate As Date
Dim HoldKPI As String
Dim HoldShopName As String
Dim HoldAreaName As String
Dim HoldMachineNumber As String
Dim HoldMachineName As String
Dim HoldRequestingTM As String
Dim HoldShift As String
Dim HoldDescription As String
Dim HoldPictureFile As String
Dim HoldAdditionalComments As String
Dim HoldStatus As String
Dim HoldPriority As String

Dim dbobject As DAO.Database
Dim KaizenRS As DAO.Recordset
Dim strquery As String

you don't need to set the variables here either.

HoldShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
HoldAreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
HoldMachineNumber = Forms![KaizenTrackingfrm]!MachineNumber.Value
HoldMachineName = Forms![KaizenTrackingfrm]!MachineName.Value

Set dbobject = CurrentDb
strquery = "SELECT * FROM KaizenTrackingtbl;"
Set KaizenRS = dbobject.OpenRecordset(strquery)

And this is muta.

DoCmd.GoToRecord , , acNewRec

'That creates a new record in the form, not the recordset you have
'opened. Change it to

KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
.... etc for each field to fill in the target table.
KaizenRS.update

and that is mari, because you can use a With statement to eliminate
all the references to KaizenRS
With KaizenRS
.addnew
!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
...
.update
End With

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub

However, when I click the "Submit" button I get an "invalid use of
Nulls" error. I have been reading through other posts on this
error, and wonder if I am missing an "Else if" statement ....but
I'm just not sure where to go with this. I'm still on a big
learning curve and would appreciate whatever help you can offer.
Thanks!

Else Ifs are not required in your code.
I suspect using the .addnew will fix the problem.
If it doesn't fix it, consider testing for empty strings as wll as
nulls.

If IsNull(Date) or [date] = "" Then
Which can be simplified to
If len ([Date] & "" ) = 0 Then

 

[rquintal]

Subject: Re: 3 linked combo boxes in a form Options
Newsgroups: Bob:microsoft.public.access.formscoding

My original reply has not appeared on my newsserver yet, so here is
a new reply.

Thank you, Bob....I was missing the "on current event" Had it for
the Shopcbo, but missed it on the equipmentcbo and was going crazy
with different combinations to try and get it to work. Can you
help with "Nulls"?

Yes, but I don't think nulls are the problem.An empty string, "",
looks just like a null, but behaves differently.

You may want to test for empty strings too, but lets leave
discussion of that issue for later.

Just one thing, beware of using Access reserved words, date is one
of them, as field or variable names.

I have the following code for the Submit function:

Private Sub CmdSubmit_Click()
If IsNull(Date) Then
MsgBox "You must select a Date."
Exit Sub
End If

[ snip of 6 additional If statements, which look ok.

SubmitKaizen
End Sub

from this code, I want to go to the following module to finish
sending the data collected in the form to the table:

Here lie some problems, and some muda, muta and mari.

Sub SubmitKaizen()
On Error GoTo Err_Submit_Click

This first batch of variable assignments is not necessary, you can
set the field values directly from the form controls.

Dim HoldKaizenNumber As Integer
Dim HoldDate As Date
Dim HoldKPI As String
Dim HoldShopName As String
Dim HoldAreaName As String
Dim HoldMachineNumber As String
Dim HoldMachineName As String
Dim HoldRequestingTM As String
Dim HoldShift As String
Dim HoldDescription As String
Dim HoldPictureFile As String
Dim HoldAdditionalComments As String
Dim HoldStatus As String
Dim HoldPriority As String

Dim dbobject As DAO.Database
Dim KaizenRS As DAO.Recordset
Dim strquery As String

you don't need to set the variables here either.

HoldShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
HoldAreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
HoldMachineNumber = Forms![KaizenTrackingfrm]!MachineNumber.Value
HoldMachineName = Forms![KaizenTrackingfrm]!MachineName.Value

Set dbobject = CurrentDb
strquery = "SELECT * FROM KaizenTrackingtbl;"
Set KaizenRS = dbobject.OpenRecordset(strquery)

And this is muta.

DoCmd.GoToRecord , , acNewRec

'That creates a new record in the form, not the recordset you have
'opened. Change it to

KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
.... etc for each field to fill in the target table.
KaizenRS.update

and that is mari, because you can use a With statement to eliminate
all the references to KaizenRS
With KaizenRS
.addnew
!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
...
.update
End With

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub

However, when I click the "Submit" button I get an "invalid use of
Nulls" error. I have been reading through other posts on this
error, and wonder if I am missing an "Else if" statement ....but
I'm just not sure where to go with this. I'm still on a big
learning curve and would appreciate whatever help you can offer.
Thanks!

Else Ifs are not required in your code.
I suspect using the .addnew will fix the problem.
If it doesn't fix it, consider testing for empty strings as wll as
nulls.

If IsNull(Date) or [date] = "" Then
Which can be simplified to
If len ([Date] & "" ) = 0 Then

 

[Opal]

My original reply has not appeared on my newsserver yet, so here is
a new reply.

Thank you, Bob....I was missing the "on current event" Had it for
the Shopcbo, but missed it on the equipmentcbo and was going crazy
with different combinations to try and get it to work. Can you
help with "Nulls"?

Yes, but I don't think nulls are the problem.An empty string, "",
looks just like a null, but behaves differently.

You may want to test for empty strings too, but lets leave
discussion of that issue for later.

Just one thing, beware of using Access reserved words, date is one
of them, as field or variable names.

I have the following code for the Submit function:

Private Sub CmdSubmit_Click()
If IsNull(Date) Then
MsgBox "You must select a Date."
Exit Sub
End If

[ snip of 6 additional If statements, which look ok.

SubmitKaizen
End Sub

from this code, I want to go to the following module to finish
sending the data collected in the form to the table:

Here lie some problems, and some muda, muta and mari.

Sub SubmitKaizen()
On Error GoTo Err_Submit_Click

This first batch of variable assignments is not necessary, you can
set the field values directly from the form controls.

Dim HoldKaizenNumber As Integer
Dim HoldDate As Date
Dim HoldKPI As String
Dim HoldShopName As String
Dim HoldAreaName As String
Dim HoldMachineNumber As String
Dim HoldMachineName As String
Dim HoldRequestingTM As String
Dim HoldShift As String
Dim HoldDescription As String
Dim HoldPictureFile As String
Dim HoldAdditionalComments As String
Dim HoldStatus As String
Dim HoldPriority As String

Dim dbobject As DAO.Database
Dim KaizenRS As DAO.Recordset
Dim strquery As String

you don't need to set the variables here either.

HoldShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
HoldAreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
HoldMachineNumber = Forms![KaizenTrackingfrm]!MachineNumber.Value
HoldMachineName = Forms![KaizenTrackingfrm]!MachineName.Value

Set dbobject = CurrentDb
strquery = "SELECT * FROM KaizenTrackingtbl;"
Set KaizenRS = dbobject.OpenRecordset(strquery)

And this is muta.> DoCmd.GoToRecord , , acNewRec

'That creates a new record in the form, not the recordset you have
'opened. Change it to

KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
... etc for each field to fill in the target table.
KaizenRS.update

and that is mari, because you can use a With statement to eliminate
all the references to KaizenRS
With KaizenRS
.addnew
!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
...
.update
End With

Exit_Submit_Click:
Exit Sub

Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click
End Sub

However, when I click the "Submit" button I get an "invalid use of
Nulls" error. I have been reading through other posts on this
error, and wonder if I am missing an "Else if" statement ....but
I'm just not sure where to go with this. I'm still on a big
learning curve and would appreciate whatever help you can offer.
Thanks!

Else Ifs are not required in your code.
I suspect using the .addnew will fix the problem.
If it doesn't fix it, consider testing for empty strings as wll as
nulls.

If IsNull(Date) or [date] = "" Then
Which can be simplified to
If len ([Date] & "" ) = 0 Then

Thank you once again, Bob.....my database is really coming together.
Just two more issues that arose after I implemented your suggestions.
Since the text and combo boxes are now unbound I have a problem with
the "KaizenNumber" text box. Before I input the code, I had this box
bound to the table and it was an "autonumber" box. How can I re-
create this with an unbound text box?
Also, you told me:

And this is muta.> DoCmd.GoToRecord , , acNewRec

'That creates a new record in the form, not the recordset you have
'opened. Change it to

KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
... etc for each field to fill in the target table.
KaizenRS.update

But when I put a record in the form and click submit, the form does
not clear (or go to a new blank record). How can I accomplish this?

 

[Bob Quintal]

Thank you once again, Bob.....my database is really coming
together. Just two more issues that arose after I implemented your
suggestions. Since the text and combo boxes are now unbound I have
a problem with the "KaizenNumber" text box. Before I input the
code, I had this box bound to the table and it was an "autonumber"
box. How can I re- create this with an unbound text box?

for creating a new record? just put it in the table as an
autonumber. when you execute the .addnew statement, the autonumber
field will get a number.

If you need to show that number before creating the record, you will
need to change the field's type to long, and use a DMax() function
call to get the largest existing number and add 1 to it.

Also, you told me:

And this is muta.> DoCmd.GoToRecord , , acNewRec

'That creates a new record in the form, not the recordset you
have 'opened. Change it to

KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
... etc for each field to fill in the target table.
KaizenRS.update

But when I put a record in the form and click submit, the form
does not clear (or go to a new blank record). How can I
accomplish this?

I'm not sure it'll work on an unbound form. You could try to put the
line into the code, but after all the other code.

If it causes an error, then you will need to set the value of each
control = ""

 

[Opal]

Thank you once again, Bob.....my database is really coming
together. Just two more issues that arose after I implemented your
suggestions. Since the text and combo boxes are now unbound I have
a problem with the "KaizenNumber" text box. Before I input the
code, I had this box bound to the table and it was an "autonumber"
box. How can I re- create this with an unbound text box?

for creating a new record? just put it in the table as an
autonumber. when you execute the .addnew statement, the autonumber
field will get a number.

If you need to show that number before creating the record, you will
need to change the field's type to long, and use a DMax() function
call to get the largest existing number and add 1 to it.

Also, you told me:

And this is muta.> DoCmd.GoToRecord , , acNewRec
'That creates a new record in the form, not the recordset you
have 'opened. Change it to
KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
... etc for each field to fill in the target table.
KaizenRS.update

But when I put a record in the form and click submit, the form
does not clear (or go to a new blank record). How can I
accomplish this?

I'm not sure it'll work on an unbound form. You could try to put the
line into the code, but after all the other code.

If it causes an error, then you will need to set the value of each
control = ""

Thanks, Again, Bob

The DMax() function seems to work ...as for the other option....

I need to clear the form after each submit, because the user may need
to enter several instances and it would be more user friendly if the
form is clear after each record is input.....how about:

..Update
..MoveNext

??

 

[Bob Quintal]

- Show quoted text -

Thank you once again, Bob.....my database is really coming
together. Just two more issues that arose after I implemented
your suggestions. Since the text and combo boxes are now
unbound I have a problem with the "KaizenNumber" text box.
Before I input the code, I had this box bound to the table and
it was an "autonumber" box. How can I re- create this with an
unbound text box?

for creating a new record? just put it in the table as an
autonumber. when you execute the .addnew statement, the
autonumber field will get a number.

If you need to show that number before creating the record, you
will need to change the field's type to long, and use a DMax()
function call to get the largest existing number and add 1 to it.

Also, you told me:

And this is muta.> DoCmd.GoToRecord , , acNewRec

'That creates a new record in the form, not the recordset you
have 'opened. Change it to

KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
... etc for each field to fill in the target table.
KaizenRS.update

But when I put a record in the form and click submit, the form
does not clear (or go to a new blank record). How can I
accomplish this?

I'm not sure it'll work on an unbound form. You could try to put
the line into the code, but after all the other code.

If it causes an error, then you will need to set the value of
each control = ""

--
Bob Quintal

PA is y I've altered my email address.

--
-
Hide quoted text -

- Show quoted text -

Thanks, Again, Bob

The DMax() function seems to work ...as for the other option....

I need to clear the form after each submit, because the user may
need to enter several instances and it would be more user friendly
if the form is clear after each record is input.....how about:

.Update
.MoveNext

??

..movenext will move the recordset opened in code, so that won't
clear an unbound form.

If you have only a few text or combo boxes to clear, setting each to
"" or to null would be simplest.

If there are a lot, you could set the .tag property of those
controls you need to clear to "Clear Me" and run the following code

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 have to mark which controls to reset because you can't reset
labels, ole objects and some others.

 

[Opal]

in
--
-
Hide quoted text -
- Show quoted text -
Thank you once again, Bob.....my database is really coming
together. Just two more issues that arose after I implemented
your suggestions. Since the text and combo boxes are now
unbound I have a problem with the "KaizenNumber" text box.
Before I input the code, I had this box bound to the table and
it was an "autonumber" box. How can I re- create this with an
unbound text box?
for creating a new record? just put it in the table as an
autonumber. when you execute the .addnew statement, the
autonumber field will get a number.
If you need to show that number before creating the record, you
will need to change the field's type to long, and use a DMax()
function call to get the largest existing number and add 1 to it.
Also, you told me:
And this is muta.> DoCmd.GoToRecord , , acNewRec
'That creates a new record in the form, not the recordset you
have 'opened. Change it to
KaizenRS.addnew
KaizenRS!ShopName = Forms![KaizenTrackingfrm]!Shopcbo.Value
KaizenRS!AreaName = Forms![KaizenTrackingfrm]!Areacbo.Value
... etc for each field to fill in the target table.
KaizenRS.update
But when I put a record in the form and click submit, the form
does not clear (or go to a new blank record). How can I
accomplish this?
I'm not sure it'll work on an unbound form. You could try to put
the line into the code, but after all the other code.
If it causes an error, then you will need to set the value of
each control = ""

Thanks, Again, Bob

The DMax() function seems to work ...as for the other option....

I need to clear the form after each submit, because the user may
need to enter several instances and it would be more user friendly
if the form is clear after each record is input.....how about:

??

.movenext will move the recordset opened in code, so that won't
clear an unbound form.

If you have only a few text or combo boxes to clear, setting each to
"" or to null would be simplest.

If there are a lot, you could set the .tag property of those
controls you need to clear to "Clear Me" and run the following code

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 have to mark which controls to reset because you can't reset
labels, ole objects and some others.

Bob,

I have 7 combo boxes and 7 text boxes that will need to
clear....however, one of the text boxes is the auto number box I set
up with DMax() function. I put the DMax() in the On Open event for
the form. Just trying to get my head around this.... If I clear this
box, then I will lose the auto numbering for the next record input.
I'm wondering if I should move the DMax() to the On Current event. I
am trying to make this form as user friendly as possible as many of
the users are not that computer literate and I am anticipating all the
concerns that will come up once the database is put into
use....."Like, why doesn't the form clear after I input a record"

 

[Bob Quintal]

Bob,

I have 7 combo boxes and 7 text boxes that will need to
clear....however, one of the text boxes is the auto number box I
set up with DMax() function. I put the DMax() in the On Open
event for the form. Just trying to get my head around this....
If I clear this box, then I will lose the auto numbering for the
next record input. I'm wondering if I should move the DMax() to
the On Current event. I am trying to make this form as user
friendly as possible as many of the users are not that computer
literate and I am anticipating all the concerns that will come up
once the database is put into use....."Like, why doesn't the form
clear after I input a record"

Then do not clear the autonumber box when you clear the other
fields. The code I posted gives you two methods to clear the boxes.

Instead, for that combobox, you probably want to call the routine
that generates the autonumber, and put the new value there. That
way, if 3 people are entering data at the same time, you will get
the correct numbre, as opposed to just adding 1 to the autonumber
that's there.

Note that when you move the Dmax to the OnCurrent Event, make sure
to test that If Me.NewRecord = True Then, so that you don't change
the existing number whenever you revisit it.

 

[Opal]

Then do not clear the autonumber box when you clear the other
fields. The code I posted gives you two methods to clear the boxes.

Instead, for that combobox, you probably want to call the routine
that generates the autonumber, and put the new value there. That
way, if 3 people are entering data at the same time, you will get
the correct numbre, as opposed to just adding 1 to the autonumber
that's there.

Note that when you move the Dmax to the OnCurrent Event, make sure
to test that If Me.NewRecord = True Then, so that you don't change
the existing number whenever you revisit it.

Okay, if I understand you correctly, I should move the DMax() to the
On Current event, and test it with the If Me.NewRecord.......and add
the "Clear me" function to the 13 other text & combo boxes? I will
give this a try
and keep my fingers crossed.....this has been quite the learning
experience for me...thank you for all your help and suggestions.

 

[Bob Quintal]

innews:[email protected]:

- Show quoted text -

I have 7 combo boxes and 7 text boxes that will need to
clear....however, one of the text boxes is the auto number
box I set up with DMax() function. I put the DMax() in the
On Open event for the form. Just trying to get my head
around this.... If I clear this box, then I will lose the
auto numbering for the next record input. I'm wondering if I
should move the DMax() to the On Current event. I am trying
to make this form as user friendly as possible as many of the
users are not that computer literate and I am anticipating
all the concerns that will come up once the database is put
into use....."Like, why doesn't the form clear after I input
a record"

Then do not clear the autonumber box when you clear the other
fields. The code I posted gives you two methods to clear the
boxes.

Instead, for that combobox, you probably want to call the
routine that generates the autonumber, and put the new value
there. That way, if 3 people are entering data at the same
time, you will get the correct numbre, as opposed to just
adding 1 to the autonumber that's there.

Note that when you move the Dmax to the OnCurrent Event, make
sure to test that If Me.NewRecord = True Then, so that you
don't change the existing number whenever you revisit it.

PA is y I've altered my email address.

-

- Show quoted text -

Bob,

I am getting a compile error on the following statment:

set ctlx = me.controls


Compile error - invalid use of Me keyword

Am I missing something?

Is the code in asub on the form? is the form open in normal view?

 

[Opal]

Then do not clear the autonumber box when you clear the other
fields. The code I posted gives you two methods to clear the boxes.

Instead, for that combobox, you probably want to call the routine
that generates the autonumber, and put the new value there. That
way, if 3 people are entering data at the same time, you will get
the correct numbre, as opposed to just adding 1 to the autonumber
that's there.

Note that when you move the Dmax to the OnCurrent Event, make sure
to test that If Me.NewRecord = True Then, so that you don't change
the existing number whenever you revisit it.

PA is y I've altered my email address.

-

- Show quoted text -

Bob,

I am getting a compile error on the following statment:

set ctlx = me.controls


Compile error - invalid use of Me keyword

Am I missing something?

 

[Bob Quintal]

Here is where I am a little confused, I put the code after the
.update

form is open and in normal view.

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

 

[Opal]

Is the code in asub on the form? is the form open in normal view?

Here is where I am a little confused, I put the code after the .update

form is open and in normal view.