Check for Existing Else go to New

[tbrogdon]

I have a form with a subform. Main form (frmProd) takes 3 criteria via
combo boxes and is bound to tblProduction. The subform (sfmProdOp) is
bound to tblProdOp and contains the details for each instance in
tblProduction.

Currently I have the following code (thanks to Marshall!) attached to
the After Update event of the final combo box of the main form:

Private Sub cboShift_AfterUpdate()
If Me.Dirty Then Me.Dirty = False
Me.sfmProdOp.SetFocus
Me.sfmProdOp.Form.cboWorkstationID.SetFocus
End Sub

This works great with a new record. However, the user sometimes puts
incomplete data in the fields in sfmProdOp or generally doesn't finish
input in one sitting. I would like for the user to be able to use the
same form, enter the same 3 criteria in the main form and if a record
does exist in tblProduction bring up the related records in sfmProdOp
from tblProdOp for editing and entering more records. Is this
possible?

Thanks,

Tim

 

[Marshall Barton]

I have a form with a subform. Main form (frmProd) takes 3 criteria via
combo boxes and is bound to tblProduction. The subform (sfmProdOp) is
bound to tblProdOp and contains the details for each instance in
tblProduction.

Currently I have the following code (thanks to Marshall!) attached to
the After Update event of the final combo box of the main form:

Private Sub cboShift_AfterUpdate()
If Me.Dirty Then Me.Dirty = False
Me.sfmProdOp.SetFocus
Me.sfmProdOp.Form.cboWorkstationID.SetFocus
End Sub

This works great with a new record. However, the user sometimes puts
incomplete data in the fields in sfmProdOp or generally doesn't finish
input in one sitting. I would like for the user to be able to use the
same form, enter the same 3 criteria in the main form and if a record
does exist in tblProduction bring up the related records in sfmProdOp
from tblProdOp for editing and entering more records. Is this
possible?

That sounds like the normal behavior if you use the subform
control's LinkMaster/Child properties. Try it if you are
not using them. If you already are using those properties,
please explain what you want that goes beyond the normal
linked subform behavior.

 

[tbrogdon]

That sounds like the normal behavior if you use the subform
control's LinkMaster/Child properties.  Try it if you are
not using them.  If you already are using those properties,
please explain what you want that goes beyond the normal
linked subform behavior.

I am using the LinkMaster/Child properties to link frmProd and
sfmProdOp. When I input data in the main form (frmProduction) that
corresponds to a record in tblProduction I get runtime error 3022
(duplicate values). You should know that the 3 fields in tblProduction
(ProductionDate, Dept, and Shift) together create a composite PK.

I just removed and reinserted sfmProdOp into frmProd just to make sure
of the LinkMaster/Child properties and they are linked on the 3
fields. When I receive the error and click "debug" it takes me to this
line of code that we discussed before:

If Me.Dirty Then Me.Dirty = False

So it seems Access is trying to save the data in the main form fields
to tblProduction and finding that they exist already in tblProduction
as a PK and won't allow a duplicate (which is correct - I don't want a
duplicate). What I would like is a "test" in the code I think where
(pardon my lack of VBA verbiage):

If the data in the 3 fields in frmProd do not exist in tblProduction
then SAVE the data in tblProduction and take me to sfmProdOp to enter
new data;
Else If the data DOES exist in tblProduction then return all the
records in tblProdOp that relate to those records in tblProduction to
sfmProdOp for further entering and editing.

Does that make sense? It definitely is not allowing that now.

Thanks,

Tim

 

[Marshall Barton]

I am using the LinkMaster/Child properties to link frmProd and
sfmProdOp. When I input data in the main form (frmProduction) that
corresponds to a record in tblProduction I get runtime error 3022
(duplicate values). You should know that the 3 fields in tblProduction
(ProductionDate, Dept, and Shift) together create a composite PK.

I just removed and reinserted sfmProdOp into frmProd just to make sure
of the LinkMaster/Child properties and they are linked on the 3
fields. When I receive the error and click "debug" it takes me to this
line of code that we discussed before:

If Me.Dirty Then Me.Dirty = False

So it seems Access is trying to save the data in the main form fields
to tblProduction and finding that they exist already in tblProduction
as a PK and won't allow a duplicate (which is correct - I don't want a
duplicate). What I would like is a "test" in the code I think where
(pardon my lack of VBA verbiage):

If the data in the 3 fields in frmProd do not exist in tblProduction
then SAVE the data in tblProduction and take me to sfmProdOp to enter
new data;
Else If the data DOES exist in tblProduction then return all the
records in tblProdOp that relate to those records in tblProduction to
sfmProdOp for further entering and editing.

Does that make sense? It definitely is not allowing that now.

Yes, it makes sense, but it is also standard for that to
happen. There is something else getting in the way and I
suspect it might be the three combo boxes.

If they are bound to tblProduction fields and used for data
entry to create new records, then you should not be typing
anything into them unless you intend to create a new record.
I would go so far as to use the Current event to lock them
on every record except a new record:
Me.cboxxx.Lock = Not Me.NewRecord

If you are using them to change the PK of an existing
tblProduction record it would be ***highly inusual*** and
require that the relationship to tblProdOp enforce
relational integrity with Cascade Updates (something I am
reluctant to do). I seriously doubt this is what you intend
to do with the combo boxes.

Instead of all that, if the combo boxes are indeed used to
locate existing records in frmProd, the combo boxes must be
**unbound** so they do not interfere with existing records.
If this is the case then, we need to come up with a
mechanism to do it.

Please comfirm or refute my assumptions before I go too far
down a potentially wrong road.

 

[tbrogdon]

If they are bound to tblProduction fields and used for data
entry to create new records, then you should not be typing
anything into them unless you intend to create a new record.

The intent in frmProd IS to create a new records - initially. And I
should clarify, I have 1 bound text field for ProductionDate and 2
bound combo boxes for Dept and Shift respectively. These 3 fields make
up the entirety of tblProduction and are together its composite PK.

If you are using them to change the PK of an existing
tblProduction record it would be ***highly inusual***

You are correct - I in no way want to change or alter any composite PK
that already exists.

Instead of all that, if the combo boxes are indeed used to
locate existing records in frmProd, the combo boxes must be
**unbound** so they do not interfere with existing records.
If this is the case then, we need to come up with a
mechanism to do it.

It is the case that I would like the combo boxes and txt field to do
double duty and both "add new" and also "retrieve-for-editing" but it
sounds like I can't which is OK. I believe I am hearing you say that
in order to after-the-fact retrieve existing records in tblProdOp (NOT
tblProduction) to "add-to"or edit I will need another form based on
tblProdOp with unbound parameter fields and probably power this with a
parameter query.

So I'll end up with a form for entering and another for "Adding-to" or
editing - does that sound right?

 

[Marshall Barton]

The intent in frmProd IS to create a new records - initially. And I
should clarify, I have 1 bound text field for ProductionDate and 2
bound combo boxes for Dept and Shift respectively. These 3 fields make
up the entirety of tblProduction and are together its composite PK.



You are correct - I in no way want to change or alter any composite PK
that already exists.


It is the case that I would like the combo boxes and txt field to do
double duty and both "add new" and also "retrieve-for-editing" but it
sounds like I can't which is OK. I believe I am hearing you say that
in order to after-the-fact retrieve existing records in tblProdOp (NOT
tblProduction) to "add-to"or edit I will need another form based on
tblProdOp with unbound parameter fields and probably power this with a
parameter query.

So I'll end up with a form for entering and another for "Adding-to" or
editing - does that sound right?

Well, yes that does sound like a viable approach. But, if
tblProduction has only those three fields, you have a
special case. The three main form control must be unbound
so they can be used for searching, but we could come up with
a mechanism to add a new record to tblProduction when there
is no existing record that matches the combo boxes.

Use the AfterUpdate event of the text box and combo boxes to
append a new record. Since we want to do the same thing
from three places, create a sub procedure to do the work:

Private Sub SaveIfNew()
Dim db As Database
Dim strSQL As String

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) Then Exit Sub
Set db = CurrentDb()
strSQL = "INSERT INTO tblProduction " _
& "(ProductionDate,Dept,Shift) " _
& Format(txtProductionDate, "\#yyyy-m-d\#") & ","_
& cboDept & "," & cboShift
db.Execute strSQL

If Dept and Shift are Text fields then the last line in the
SQL statement would be:
& """" & Dept & """,""" & Shift & """"

Note that the Execute method will fail if there is an
existing record, but, without the dbFailOnError argument, it
will fail silently so you don't really have to do anything
about it.

Also note that I used txtProductionDate, cboDept and
cboShift as the names of the (now) unbound controls to
emphasize that they are not directly related to the table
fields. If you change the control names as I suggest, then
be sure to change the subform control's Link Master property
too.

Remove the main form's Record source and the text box and
combo boxes' ControlSource. Also remove the existing
AfterUpdate event procedures and set the three controls'
AfterUpdate **property** to:
=SaveIfNew()

You would only need a separate bound form for tblProduction
if you should decide that you have to modify existing
records, but, as I said before, this would involve several
other issues beyond just creating another form.

 

[tbrogdon]

Hi Marshall,

I did everything the way I understood from your post. When I open the
main form - before it opens - 3 parameter requests pop up in
succession. The first requests "ProductionDate." The second requests
"Dept." And the third "Shift." After making an entry into each of the
3 parameter requests, the main form finally opens and is blank. I've
checked the underlying tables and the parameter data doesn't seem to
be captured anywhere.

I started by removing the main form's Record source and the Control
sources for txtProductionDate, cboDept, and cboShift (which I renamed
per your instructions). I removed all of the code from the main form
except for a SetFocus that sets the focus on txtProductionDate when
the form is opened. Then, and I think this is where I messed up, I
selected the uppermost left corner of the main form and right-clicked
and then selected "Build Event" and that is where I placed the code:

Private Sub SaveIfNew()
Dim db As Database
Dim strSQL As String
'code goes here...
db.Execute strSQL

Then I added the line =SaveIfNew() to the AfterUpdate property for the
corresponding fields in sfmProdOp. Speaking of which I just realized
that the field -names- in sfmProdOp are respectively txtDate, cboDept,
and cboShift. The control sources are tblProdOp.ProductionDate,
tblProdOp.Dept, and tblProdOp.Shift (in other words: ProductionDate,
Dept, Shift). This could also be the problem.

Also, I checked the LinkMaster/Child properties in sfmProdOp and they
are set as follows:
Link Child Fields: ProductionDate, Dept, Shift
Link Master Fields: txtProductionDate, cboDept, cboShift


What do you think? Is it because tblProdOp, upon which sfmProdOp is
based, is linked to tblProduction on ProductionDate, Dept, and Shift
and won't allow a record to be entered until an entry is made into
tblProduction? I have another Update query that acts just like this.
If I haven't made an entry in tblProduction I can't run the update
query in yet another table that is also linked to tblProduction on the
same three fields.

Is there a way to "stall" sfmProdOp in order to allow data to be
entered in frmProd before sfmProdOp (which by the way is on a tabbed
control - page (0) in frmProd) makes it decision to ask for
parameters?

 

[Marshall Barton]

I did everything the way I understood from your post. When I open the
main form - before it opens - 3 parameter requests pop up in
succession. The first requests "ProductionDate." The second requests
"Dept." And the third "Shift." After making an entry into each of the
3 parameter requests, the main form finally opens and is blank. I've
checked the underlying tables and the parameter data doesn't seem to
be captured anywhere.

I started by removing the main form's Record source and the Control
sources for txtProductionDate, cboDept, and cboShift (which I renamed
per your instructions). I removed all of the code from the main form
except for a SetFocus that sets the focus on txtProductionDate when
the form is opened.

Not important, but there is no need to use SetFocus when the
form first opens. Just set the main form controls' TabIndex
appropriately and the form will automatically set the focus
to the first control in the tab order. Setting the tab
index for all the controls is easy to do using the View -
Tab Order menu item.

Then, and I think this is where I messed up, I
selected the uppermost left corner of the main form and right-clicked
and then selected "Build Event" and that is where I placed the code:

Private Sub SaveIfNew()
Dim db As Database
Dim strSQL As String
'code goes here...
db.Execute strSQL

It's not clear what you ended up with in the main form's
module. The Build Event ... button opens the form's module
all right, but it also creates a skeletal event procedure
that might confuse things. Post a Copy/Paste of the module
so I can review it.

Then I added the line =SaveIfNew() to the AfterUpdate property for the
corresponding fields in sfmProdOp.

That doesn't sound right. You were supposed to put that in
the AfterUpdate property of the three main form controls.

Speaking of which I just realized
that the field -names- in sfmProdOp are respectively txtDate, cboDept,
and cboShift. The control sources are tblProdOp.ProductionDate,
tblProdOp.Dept, and tblProdOp.Shift (in other words: ProductionDate,
Dept, Shift). This could also be the problem.

That shouldn't be a problem, but your use of the word field
is ambiguous. Tables and queries have fields, forms and
reports have controls. If you meant control names instead
of field names, then it should not be a problem. If you
really meant field names, then I am confused, because I
thought the table fields did not have the txt/cbo prefixes.

Note that there should be no need to use table names in the
control source. That would only be appropriate if the
record source query had two fields with the same names,
which is not a good thing.

Also, I checked the LinkMaster/Child properties in sfmProdOp and they
are set as follows:
Link Child Fields: ProductionDate, Dept, Shift
Link Master Fields: txtProductionDate, cboDept, cboShift
Right.


What do you think? Is it because tblProdOp, upon which sfmProdOp is
based, is linked to tblProduction on ProductionDate, Dept, and Shift
and won't allow a record to be entered until an entry is made into
tblProduction?

The fact that you are being prompted for those three values
is definitely a symptom of a problem. The prompts are
coming from one or more queries so check if the control name
changes need to be made in the combo box's row source
queries.

I am now concerned about the subform's record source. It
should probably be a query based solely on tblProdOp. This
query should not join to tblProduction. The only reason for
using a query instead of just the table is to sort the
records. If this is not clear, post a Copy/Paste of the
subform's record source query's SQL statement.

I have another Update query that acts just like this.
If I haven't made an entry in tblProduction I can't run the update
query in yet another table that is also linked to tblProduction on the
same three fields.

I don't want to clutter the issue by discussing other tables
at this time.

Is there a way to "stall" sfmProdOp in order to allow data to be
entered in frmProd before sfmProdOp (which by the way is on a tabbed
control - page (0) in frmProd) makes it decision to ask for
parameters?

Once we get everything else straightened out this should not
happen so there won't be any reason to do anything.

 

[tbrogdon]

Hi Marshall,

Thank you for your ongoing support. I only used the tables names in my
post to clarify what the particular control sources for those controls
are. More clarity, I typed "field" when I meant "control."

I also wanted to give you a heads up that it will be later tonight
before I can dig into this further. Please look for my post at your
convenience. I am going to make the changes you suggest.

Again,

Thank you for all of your help,

Tim

 

[tbrogdon]

It's not clear what you ended up with in the main form's

module. The Build Event ... button opens the form's module
all right, but it also creates a skeletal event procedure
that might confuse things. Post a Copy/Paste of the module
so I can review it.

This is what I have in the module of the main form (I literally copy/
pasted your code into the module and verified that the main form
controls were named in correspondence with the code:

Private Sub SaveIfNew()
Dim db As Database
Dim strSQL As String

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) Then Exit Sub
Set db = CurrentDb()
strSQL = "INSERT INTO tblProduction " _
& "(ProductionDate,Dept,Shift) " _
& Format(txtProductionDate, "\#yyyy-m-d
\#") & ","_
& cboDept & "," & cboShift
db.Execute strSQL

End Sub

That doesn't sound right. You were supposed to put that in
the AfterUpdate property of the three main form controls.

Sorry about that. I did actually add =SaveIfNew to the After Update
event for the corresponding fields of the main form - not sfmProdOp.

The fact that you are being prompted for those three values
is definitely a symptom of a problem. The prompts are
coming from one or more queries so check if the control name
changes need to be made in the combo box's row source
queries.

I am now concerned about the subform's record source. It
should probably be a query based solely on tblProdOp. This
query should not join to tblProduction. The only reason for
using a query instead of just the table is to sort the
records. If this is not clear, post a Copy/Paste of the
subform's record source query's SQL statement.

The idea of a query is clear but I am unsure how to implement it in
the subform. Should I have a parameter query based on the 3 controls
from the main form? And if so, do I set leave the Link Master/Child
properties the same? How do I have the query of the subform wait for
the controls in the main form to populate a new record in tblProductio
before running?

Also, just for my own clarification and in light of the difference
between fields and controls, regarding the Link Master/Child
properties; do those links link the fields in the underlying tables
behind a form and in the case of the unbound controls in the main form
link the unbound controls themselves in the main form to the bound
fields of the subform?

I have double checked all of the code, linkage, control sources
references and I am still prompted for the criteria mentioned above.

Thanks again,

Tim

 

[tbrogdon]

I am now concerned about the subform's record source.  It
should probably be a query based solely on tblProdOp.  This
query should not join to tblProduction.  The only reason for
using a query instead of just the table is to sort the
records.  If this is not clear, post a Copy/Paste of the
subform's record source query's SQL statement.

The record source's sql for sfmProdOp is:

SELECT tblProdOp.ProdOpID, tblProdOp.ProductionDate, tblProdOp.Dept,
tblProdOp.Shift, tblProdOp.WorkstationID, tblProdOp.PartID,
tblProdOp.OpStepNum, tblProdOp.Setup, tblProdOp.Operator1,
tblProdOp.Operator2, tblProdOp.QtyRun, tblProdOp.QtyScrap
FROM tblProdOp;

 

[tbrogdon]

The record source's sql for sfmProdOp is:

SELECT tblProdOp.ProdOpID, tblProdOp.ProductionDate, tblProdOp.Dept,
tblProdOp.Shift, tblProdOp.WorkstationID, tblProdOp.PartID,
tblProdOp.OpStepNum, tblProdOp.Setup, tblProdOp.Operator1,
tblProdOp.Operator2, tblProdOp.QtyRun, tblProdOp.QtyScrap
FROM tblProdOp;

Does the record source sql for sfmProdOp need a WHERE clause that says
something like:

WHERE ((tblProdOp.Department)=[forms]![frmProd]![cboDept]), etc. 'and
of course I am unsure if that is the correct syntax.

 

[Marshall Barton]

The record source's sql for sfmProdOp is:

SELECT tblProdOp.ProdOpID, tblProdOp.ProductionDate, tblProdOp.Dept,
tblProdOp.Shift, tblProdOp.WorkstationID, tblProdOp.PartID,
tblProdOp.OpStepNum, tblProdOp.Setup, tblProdOp.Operator1,
tblProdOp.Operator2, tblProdOp.QtyRun, tblProdOp.QtyScrap
FROM tblProdOp;

As long the field names in table tblProdOp are still named
ProductionDate, Dept, Shift, that looks OK to me. Maybe you
would like to add an ORDER BY clause?

But that also means we have to look for another query that
contains the names that you are being prompted to enter.
What other queries are involved in all this? The only other
ones I am aware of is the two combo box's row source query.
Do you have any other queries in these forms? If so, and
you don't see the problem, post them and I'll take a look.

 

[tbrogdon]

But that also means we have to look for another query that
contains the names that you are being prompted to enter.
What other queries are involved in all this?  The only other
ones I am aware of is the two combo box's row source query.
Do you have any other queries in these forms?  If so, and
you don't see the problem, post them and I'll take a look.

In sfmProdOp, the control source for control named txtProductionDate
is ProductionDate (from tblProdOp); for cboDept (from sfmProdOp), the
control source is Dept (i.e., tblProdOp.Dept) and the Row Source is:
SELECT tblDepartment.Dept FROM tblDepartment; for cboShift (from
sfmProdOp), the control source is Shift (i.e., tblProdOp.Shift) and
the Row Source is: SELECT tblShift.Shift FROM tblShift

Controls Workstation, PartsID, Setup, Operator1, Operator2 all have
similar Row Sources as Dept and Shift above. Setup, Operator1, and
Operator2 all link to tblEmployees (e.g., SELECT tblParts.PartsID FROM
tblParts)

The only other thing I can think of reporting that is pertinent is
that tblProdOp has a single field autonumber PK called ProdOpID. I had
originally wanted to have another composite PK but it would have had
to include so many fields that I thought it would be truly difficult
to use. The PK for tblProdOp does not link to anything else at this
point in time.

 

[Marshall Barton]

This is what I have in the module of the main form (I literally copy/
pasted your code into the module and verified that the main form
controls were named in correspondence with the code:

Private Sub SaveIfNew()
Dim db As Database
Dim strSQL As String

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) Then Exit Sub
Set db = CurrentDb()
strSQL = "INSERT INTO tblProduction " _
& "(ProductionDate,Dept,Shift) " _
& Format(txtProductionDate, "\#yyyy-m-d
\#") & ","_
& cboDept & "," & cboShift

You need a space between the last " and the _

& Format(txtProductionDate, "\#yyyy-m-d\#") & "," _

Access should have given you an error message when you tried
to run that code. In the future, you should use the Debug -
Compile menu item to check for syntax errors right after you
change any VBA code. VERY IMPORTANT - ***never*** edit a
form or report's code unless the form/report is in design
view.

Sorry about that. I did actually add =SaveIfNew to the After Update
event for the corresponding fields of the main form - not sfmProdOp.

Please use Copy/Paste when posting code, queries and
property expressions so we don't waste time discussing
typos. The **controls** on the main form should have their
AfterUpdate property set to:
=SaveIfNew()
The = and the ( ) are required.

The idea of a query is clear but I am unsure how to implement it in
the subform. Should I have a parameter query based on the 3 controls
from the main form?
No!

And if so, do I set leave the Link Master/Child
properties the same? How do I have the query of the subform wait for
the controls in the main form to populate a new record in tblProductio
before running?

The Link Master/Child properties take care of all that.
Don't go off on that tangent.

Also, just for my own clarification and in light of the difference
between fields and controls, regarding the Link Master/Child
properties; do those links link the fields in the underlying tables
behind a form and in the case of the unbound controls in the main form
link the unbound controls themselves in the main form to the bound
fields of the subform?

Exactly!

The LinkMasterFields property can (and often does) refer to
controls on the main form. The LinkChildFields property
must refer to fields in the subform record source
table/query.

I have double checked all of the code, linkage, control sources
references and I am still prompted for the criteria mentioned above.

Keep looking ;-)

 

[Marshall Barton]

The record source's sql for sfmProdOp is:

SELECT tblProdOp.ProdOpID, tblProdOp.ProductionDate, tblProdOp.Dept,
tblProdOp.Shift, tblProdOp.WorkstationID, tblProdOp.PartID,
tblProdOp.OpStepNum, tblProdOp.Setup, tblProdOp.Operator1,
tblProdOp.Operator2, tblProdOp.QtyRun, tblProdOp.QtyScrap
FROM tblProdOp;

Does the record source sql for sfmProdOp need a WHERE clause that says
something like:

WHERE ((tblProdOp.Department)=[forms]![frmProd]![cboDept]), etc. 'and
of course I am unsure if that is the correct syntax.

NO!

The LinkMaster/Child properties take care of all that.

 

[Marshall Barton]

In sfmProdOp, the control source for control named txtProductionDate
is ProductionDate (from tblProdOp); for cboDept (from sfmProdOp), the
control source is Dept (i.e., tblProdOp.Dept) and the Row Source is:
SELECT tblDepartment.Dept FROM tblDepartment; for cboShift (from
sfmProdOp), the control source is Shift (i.e., tblProdOp.Shift) and
the Row Source is: SELECT tblShift.Shift FROM tblShift

Controls Workstation, PartsID, Setup, Operator1, Operator2 all have
similar Row Sources as Dept and Shift above. Setup, Operator1, and
Operator2 all link to tblEmployees (e.g., SELECT tblParts.PartsID FROM
tblParts)

The only other thing I can think of reporting that is pertinent is
that tblProdOp has a single field autonumber PK called ProdOpID. I had
originally wanted to have another composite PK but it would have had
to include so many fields that I thought it would be truly difficult
to use. The PK for tblProdOp does not link to anything else at this
point in time.

That all looks good to me and the autonumber PK is pretty
standard. Is there any VBA code in the subform's module
that runs a query? How about event macros?

How about the main form? Check those combo boxes too.

Is there any other VBA code or macros in the main form?

Grasping at straws now but a control source expression with
a domain aggregate function (DLookup, DCount, DSum, etc)
could also cause a prompt.

 

[tbrogdon]

Is there any VBA code in the subform's module

that runs a query?  How about event macros?

No and No.

How about the main form?  Check those combo boxes too.
No.

Is there any other VBA code or macros in the main form?
No

Grasping at straws now but a control source expression with
a domain aggregate function (DLookup, DCount, DSum, etc)
could also cause a prompt.

No there also.

The controls on the main form (txtProductionDate, cboDept, and
cboShift) all have their AfterUpdate event set to =SaveIfNew().

Main form Record Source is not set (in other words that property is
blank). There are no events showing at all in the main form's Event
tab.

Subform container: Source Object is sfmProdOp; Link Child Fields is
ProductionDate;Dept;Shift; Link Master Fields is
txtProductionDate;cboDept;cboShift.

sfmProdOp controls are as follows:
txtProductionDate: Control Source - ProductionDate; no event at all
cboDept: Control Source is Dept; no event at all; Row Source - SELECT
Department.Dept FROM Department; no events at all
cboShift: Control Source is Shift; no event at all; Row Source -
SELECT Shift.Shift FROM Shift; no events at all

You need a space between the last " and the _

& Format(txtProductionDate, "\#yyyy-m-d\#") & "," _

I also double checked the space at the end of the code that you
suggested earlier. It was missing. And I never even view code unless I
am in design mode.

Now when I attempt to open frmProd, I am stillprompted for the three
parameters which I enter. Upon entering the final data (Shift), frm
Prod opens with the three controls empty in the main form. When I
enter data in the first control (txtProduction) I receive the
following error: "The expression After Update you entered as the event
property setting produced the following error: The expression you
entered has a function name that Microsoft Office Access can't find."

Again, I entered this code by left-clicking the upper left corner of
frmProd which resulted in the square in the corner having a black dot
in the middle of it. Then I right clicked to get a popup menu from
which I selected build event where I went to the next available space
and pasted this code:

Private Sub SaveIfNew()
Dim db As Database
Dim strSQL As String

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) Then Exit Sub
Set db = CurrentDb()
strSQL = "INSERT INTO tblProduction " _
& "(ProductionDate,Dept,Shift) " _
& Format(txtProductionDate, "\#yyyy-m-d
\#") & "," _
& cboDept & "," & cboShift
db.Execute strSQL

End Sub

When I select View:Code from the pull down menus at the top a module
opens up that let's me view the above code. It is listed under
(General) SaveIfNew.

The name in the caption at the top of the code window is: Microsoft
Visual Basic - "Name of DB" - [Form_frmProd (Code)]

Past this I don't know what else to look for.

 

[Marshall Barton]

No and No.


No there also.

The controls on the main form (txtProductionDate, cboDept, and
cboShift) all have their AfterUpdate event set to =SaveIfNew().

Main form Record Source is not set (in other words that property is
blank). There are no events showing at all in the main form's Event
tab.

Don't forget to check the main form combo boxes' row source
query too.

Subform container: Source Object is sfmProdOp; Link Child Fields is
ProductionDate;Dept;Shift; Link Master Fields is
txtProductionDate;cboDept;cboShift.

sfmProdOp controls are as follows:
txtProductionDate: Control Source - ProductionDate; no event at all
cboDept: Control Source is Dept; no event at all; Row Source - SELECT
Department.Dept FROM Department; no events at all
cboShift: Control Source is Shift; no event at all; Row Source -
SELECT Shift.Shift FROM Shift; no events at all




I also double checked the space at the end of the code that you
suggested earlier. It was missing. And I never even view code unless I
am in design mode.

Now when I attempt to open frmProd, I am stillprompted for the three
parameters which I enter.

This is getting frustrating. Those names have got to be in
a query from somewhere, we just have to find it.

Upon entering the final data (Shift), frm
Prod opens with the three controls empty in the main form. When I
enter data in the first control (txtProduction) I receive the
following error: "The expression After Update you entered as the event
property setting produced the following error: The expression you
entered has a function name that Microsoft Office Access can't find."

That error says that the function is not declared properly.
I am concerned that you are using the Build Event shortcut
menu to do it. Build Event is intended for what it says,
creating event procedures, and might somehow be getting in
the way of creating an oedinary function procedure.

To double check the entire module, use the VBA editor's
Tools - Options menu item. On the Editor tab, check the box
for Default to Full Module View. Then you can see all the
code in the module and check for inconsistencies. If you
have trouble interpreting what's there, post the whole thing
for me to look at.

While you are in the module, use the Debug - Compile menu
item to check for other problems.

Again, I entered this code by left-clicking the upper left corner of
frmProd which resulted in the square in the corner having a black dot
in the middle of it. Then I right clicked to get a popup menu from
which I selected build event where I went to the next available space
and pasted this code:

Private Sub SaveIfNew()
Dim db As Database
Dim strSQL As String

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) Then Exit Sub
Set db = CurrentDb()
strSQL = "INSERT INTO tblProduction " _
& "(ProductionDate,Dept,Shift) " _
& Format(txtProductionDate, "\#yyyy-m-d
\#") & "," _
& cboDept & "," & cboShift
db.Execute strSQL

End Sub

When I select View:Code from the pull down menus at the top a module
opens up that let's me view the above code. It is listed under
(General) SaveIfNew.

Ah ha! That was supposed to be a Function, not a Sub. It
should be:

Private Function SaveIfNew()

 

[tbrogdon]

I changed "Sub" to "Function." I also did everything else you
suggested including compiling the code with no problems or errors.

The only code I have associated with frmProd is the code for
SaveIfNew().

In frmProd, txtProductionDate has no row source. Dept and Shift both
do have Row Sources: Dept is SELECT Department.Dept FROM Department
and Shift is SELECT Shift.Shift FROM Shift.

Changing "Sub" to "Function" resulted in a change in the error message
and behavior. Now, when I open frmProd the parameter boxes still come
up requesting "Production", "Dept", and "Shift." However, if I
<cancel> out of the first one I am taken directly to frmProd to enter
the date in txtProductionDate. I can now ener the date, dept, and
shift and THEN when I hit enter I receive the following message:

"The expression After Update that you entered as the event property
produced the following error: *The expression may not result in the
name of a macro, the name of a user-defined function, or [Event
Procedure]; * There may have been an error evaluating the function,
event, or macro."

I don't have any macros at all any where in the db. There are no
events at all on the properties page of frmProd.

Have I put the =SaveIfNew in the incorrect place? In frmProd, I
select, for instance, txtProductionDate and right-click it which
brings up a menu from which I select the properties for the control. I
select the "Event" tab and click to the right of "After Update"
placing the cursor there. Then I type in =SaveIfNew() and hit <enter>.
I do the same with cboDept and cboShift.

Remembering that sfmProdOp is the child of frmProd, I am wondering if
Access is not trying to load sfmProdOp with its query as a Record
Source before an entry is made in tblProduction via the unbound
controls which should be generated by frmProd before the decision is
made as to how to approach sfmProdOp either as a query or as a new
record? This would bring up the parameter requests wouldn't it or am I
going down the rabbit hole? The text of the parameters requested
correspond directly to the controls Control Source in sfmProdOp and
also to the names of the fields in tblProduction.

Should this part of the code be referring to the "Name" of each of the
unbound controls in frmProd or to the fields in tblProduction? I am
assuming that the former are the fieldsand the latter are the control
names which is how I have it configured:

strSQL = "INSERT INTO tblProduction " _
& "(ProductionDate,Dept,Shift) " _
& Format(txtProductionDate, "\#yyyy-m-d
\#") & "," _
& cboDept & "," & cboShift
db.Execute strSQL