Check for Existing Else go to New

[tbrogdon]

Marshall,

I was able to resolve the parameter issue. It was a misspelling of
tblProduction. I am sorry for the time it took.

Now the form opens just fine with no parameter requests but it is not
saving the data that I input into the controls on frmProd.

Again, here is the copy/pasted code from the form's code module:

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

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

End Function

Is it exiting the function after:

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) Then Exit Function

 

[Marshall Barton]

I was able to resolve the parameter issue. It was a misspelling of
tblProduction. I am sorry for the time it took.

Now the form opens just fine with no parameter requests but it is not
saving the data that I input into the controls on frmProd.

Again, here is the copy/pasted code from the form's code module:

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

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

End Function

Is it exiting the function after:

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) Then Exit Function

Whew! Spelling problems can be a bear to uncover.

The function will do that if there is nothing in the text
box or one of the combo boxes. It is supposed to exit at
that point until all three controls have a value. If you
did enter a value into each control, then something else is
wrong. Could be some other code that is erasing a value or
myabe another dreaded spelling error.

What happens if you enter three values that already exist?

Double check the above code. I used Production for the
table name. Should it be tblProduction?

There should be at least two other lines in the form's
module so I am still questioning if have viewed the full
module. At least, make sure that you have these two lines
at the very top of the module (before the function):
Option Compare Database
Option Explicit

After we get this sorted out, remind me to ask you what
purpose tblProduction serves. From what you've described,
it seems like tblProdOp can only have one record that links
to one record in tblProduction. If that's the case and
there are no other fields in tblProduction, then I don't see
why it should exist.

 

[tbrogdon]

What happens if you enter three values that already exist?

I still get 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."

Double check the above code.  I used Production for the
table name.  Should it be tblProduction?

No, the name of the table actually is Production without the leading
"tbl." I've been using that prefix in our posts for clarity and
stripping it when I paste into the code module. Everything else is
named exactly the same as the code. The fields ProductionDate, Dept,
and Shift are exactly that in the table and the controls are
txtProductionDate, cboDept, and cboShift. I've gone over that with a
fine tooth comb. I say that and am now going to go over it again.

There should be at least two other lines in the form's
module so I am still questioning if have viewed the full
module.  At least, make sure that you have these two lines
at the very top of the module (before the function):
Option Compare Database
Option Explicit

I was missing the Option Explicit line. I did insert it and am still
receiving the same message.

 From what you've described,
it seems like tblProdOp can only have one record that links
to one record in tblProduction.  

Actually tblProdOp has many, many records that link to tblProduction
on those three fields. Each record is an instance of any given
operator(s) with any given part(s) at any given workstation, etc.

Tim

 

[Marshall Barton]

What happens if you enter three values that already exist?

I still get 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."

That means the function name in the AfterUpdate property
does not agree with the function name in the module, the
function's code does not compile without error or that the
function generates an error when it runs. (The latter cause
would normally produce a different error message, but who
knows?)

I still suspect a spelling problem (e.g. lower case L
instead of Upper case i). Maybe you should try retyping
SaveIfNew in both places.

I just reviewed the function again and I can't tell if it's
line wrapping in your post, but it looks like there could be
an extra space or a new line after the d and before the \#
in the line:

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

I was missing the Option Explicit line. I did insert it and am still
receiving the same message.

Did the module still compile without error after you added
that line?

Actually tblProdOp has many, many records that link to tblProduction
on those three fields. Each record is an instance of any given
operator(s) with any given part(s) at any given workstation, etc.

Oh, ok. But, without any other fields in table Production,
the values in tblProdOp will serve whatever purpose table
Production has, or do you intend to add more fields to
Production? This is kind of a side issue, so don't let it
get in the way of getting the form operational.

 

[tbrogdon]

That means the function name in the AfterUpdate property
does not agree with the function name in the module, the
function's code does not compile without error or that the
function generates an error when it runs.  (The latter cause
would normally produce a different error message, but who
knows?)

Is it possible to debug the code as it runs? Maybe we could find the
culprit that way.

I still suspect a spelling problem (e.g. lower case L
instead of Upper case i).  Maybe you should try retyping
SaveIfNew in both places.

I re-typed the function name SaveIfNew() in the module and
=SaveIfNew() in all three AfterUpdates. Didn't work. Then, I copy/
pasted from the module into the AfterUpdates adding the "=" of course
with no space,also didn't work.

I just reviewed the function again and I can't tell if it's
line wrapping in your post, but it looks like there could be
an extra space or a new line after the d and before the \#
in the line:

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

That wrap just happened in the post.It's not in the code. I'm
wondering if the date formatting in the code could be throwing it off.
The field ProductionDate is not formatted like yyyy-m-d in table
Production. It's Format property is set to Short Date with an input

Did the module still compile without error after you added
that line?

Yes, it does compile without error even after adding "Option
Explicit." Here is the complete module:

Option Compare Database
Option Explicit

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

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

End Function

There isn't any other code in the module attached to frmProd.

Oh, ok.  But, without any other fields in table Production,
the values in tblProdOp will serve whatever purpose table
Production has, or do you intend to add more fields to
Production?  This is kind of a side issue, so don't let it
get in the way of getting the form operational.

Just as a sidebar - virtually every major question/task asked of this
db is defined by Date, Dept, and Shift. There are actually other
tables that use the same three fields as composite foreign keys as
well. I tried to come up with a different design but coming back to
those 3 fields in multiple tables (e.g., tblEmployeeProduction which
captures the actual hours worked by an employee in a particular
capacity during a specific combination of Date, Dept, and Shift. In
this case, employees can switch job functions, depts, and work across
shifts) I would be very open to any insight into a different given the
business rules/needs - but like you mentioned - that would probably
just muddy up my already "murky" current predicament with this form!
<grin>

I am really beginning to wonder if somehow I have put the code in the
wrong place. I've checked that it is associated to frmProd by opening
the Project Manager and selecting frmProd which immediately pops up
the code that I pasted in above. How would you open up the form's
module to place the code if you were doing it from scratch?

 

[Marshall Barton]

Is it possible to debug the code as it runs? Maybe we could find the
culprit that way.

Several ways. I think in this situation, setting break
points would be the most productive. To set a break point,
just click in the left margin (gray) of the line where you
want execution to stop, a large, dark red dot will show in
the margin anf the statement will turn red.

Then enter values in the form controls and the code will
stop on the line with the break point. DO NOT make any code
changes, not even a useless space or tab, while the code is
paused. You can see the value of any control reference or
variable just by hovering the mouse over it. The Immediate
window can be useful for calculating and/or displaying
values too. A quick check to see if the function is
actually being called is to place a break point near the top
of the code (e.g. the If statemenr). To resume code
execution, use the Run - Continue menu item.

I re-typed the function name SaveIfNew() in the module and
=SaveIfNew() in all three AfterUpdates. Didn't work. Then, I copy/
pasted from the module into the AfterUpdates adding the "=" of course
with no space,also didn't work.


That wrap just happened in the post.It's not in the code. I'm
wondering if the date formatting in the code could be throwing it off.
The field ProductionDate is not formatted like yyyy-m-d in table
Production. It's Format property is set to Short Date with an input
mask of 99/99/0000; <grasping at straws again>

Not as long as the ProductionDate field is a Date/Time field
(not a Text field).

Yes, it does compile without error even after adding "Option
Explicit." Here is the complete module:

Option Compare Database
Option Explicit

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

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

End Function

There isn't any other code in the module attached to frmProd.

Ok. I was hoping for something

I am really beginning to wonder if somehow I have put the code in the
wrong place. I've checked that it is associated to frmProd by opening
the Project Manager and selecting frmProd which immediately pops up
the code that I pasted in above. How would you open up the form's
module to place the code if you were doing it from scratch?

frmProd is the right place. The main form, where you enter
the values of the controls used in the LinkMasterFields
property. The same controls with =SaveIfNew() in the
AfterUpdate property. Everything we are doing is in
frmProd.

We are not doing anything in the subform and, as far as I
know, the subform should not have a module at all (HasModule
property is set to No). Whatever controls are in the
subform should not have any of their event properties set.

The way I get to a form's module is to display the form in
design view an use the Access (not VBA) window's Form Design
toolbar's Code button. But there are several other ways.

 

[tbrogdon]

Marshall,

I wanted to ask you before I change anything...is it possible that
when I originally copy/pasted the code from your post into my module
that spaces were added, formatting was changed, line breaks were
inserted, etc.and could those kinds of anomalies have affected the
code? I of course am unableto see formatting marks so i don't know if
that is even an issue.

I will be trying your debugging tips in awhile.

Tim

 

[tbrogdon]

Hi Marshall,

I was just wondering if the sql might need to read something like,
"txtProductionDate AS Date, cboDept AS Dept, cboShift AS Shift."

Am I barking up the wrong tree?

Tim

 

[Marshall Barton]

I wanted to ask you before I change anything...is it possible that
when I originally copy/pasted the code from your post into my module
that spaces were added, formatting was changed, line breaks were
inserted, etc.and could those kinds of anomalies have affected the
code? I of course am unableto see formatting marks so i don't know if
that is even an issue.

Certainly, it's possible and that's why I asked you to
Copy/Paste it back. We even double checked a couple of
spaces that didn't look right. But that kind of problem
comes from the posts, not from the VBA editor.

The VBA Editor generally does not care about extra spaces
between elements in a statement and will throw them away for
you. The ones you have to be concerned about are extra
spaces in a name and inside quotes. OTOH, a missing space
can cause trouble if two names run together.

Another important point is when a statement is continued on
more than one line. A statement can be continued on another
line by using space+underscore at the end of a line so make
sure there is a space before the underscore at the end of a
line that is continued on the next line.

 

[Marshall Barton]

I was just wondering if the sql might need to read something like,
"txtProductionDate AS Date, cboDept AS Dept, cboShift AS Shift."

Am I barking up the wrong tree?

Yeah, you're barking up a nonexistent tree ;-)

However, you did just shine a big spotlight on (one of?) my
blind spot. I left out some critical syntax in this kind of
SQL statement :-(

It should have been:

strSQL = "INSERT INTO Production " _
& "(ProductionDate,Dept,Shift) " _
& "VALUES(" _
& Format(txtProductionDate, "\#yyyy-m-d\#") _
& "," & cboDept & "," & cboShift & ")"

but I don't think we got far enough for that to show up yet.

If I haven't lost track of the issues, the current problem
is figuring out why the SaveIfNew function is not being
called from the txtProductionDate, cboDept and cboShift
controls' AfterUpdate events.

 

[tbrogdon]

If I haven't lost track of the issues, the current problem
is figuring out why the SaveIfNew function is not being
called from the txtProductionDate, cboDept and cboShift
controls' AfterUpdate events.

Yup, that is the issue. I set breakpoints throughout the code. Setting
breakpoints on:

strSQL = "INSERT INTO Production" _
& "(ProductionDate, Dept, Shift)" _
& "VALUES(" _
& Format(txtProductionDate, "\#yyyy-m-d\#") _
& "," & cboDept & "," & cboShift & ")"

and entering 11/13/2007, Hard Tool, 1 into the controls on frmProd
produced the results we want, I believe. I hovered the cursor above
the control names in the code and each one showed the values that I
input.

Setting the breakpoint on:

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) _
Then Exit Function

and only entering data into txtProductionDate and cboDept and leaving
cboShift resulted in the values I placed in the first two showing when
hovering and cboShift showing Null.

The other thing I did (and I am only guessing) is that I set the
breakpoint on:

Set db = CurrentDB()

When the code broke there, I dragged the breakpoint down to the end of
the definition of strSQL and hit the Run - Continue menu item. I
received the following message (and again I am just guessing that I
did something worthwhile):

"The expression After Update you entered as the event property setting
produced the following error: Object variable or With block variable
not set. *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, macro."

 

[Marshall Barton]

Yup, that is the issue. I set breakpoints throughout the code. Setting
breakpoints on:

strSQL = "INSERT INTO Production" _
& "(ProductionDate, Dept, Shift)" _
& "VALUES(" _
& Format(txtProductionDate, "\#yyyy-m-d\#") _
& "," & cboDept & "," & cboShift & ")"

and entering 11/13/2007, Hard Tool, 1 into the controls on frmProd
produced the results we want, I believe. I hovered the cursor above
the control names in the code and each one showed the values that I
input.

Hold on a minute. I thought Dept and Shift were both number
type fields in the Production table. If Dept can be "Hard
Tool", then Dept must be a Text field. In that case, the
query needs to be:

strSQL = "INSERT INTO Production" _
& "(ProductionDate, Dept, Shift)" _
& "VALUES(" _
& Format(txtProductionDate, "\#yyyy-m-d\#") _
& ",""" & cboDept & """," & cboShift & ")"

A similar change would be required if Shift is also a Text
field.

 

[tbrogdon]

Hold on a minute.  I thought Dept and Shift were both number
type fields in the Production table.  If Dept can be "Hard
Tool", then Dept must be a Text field.  In that case, the
query needs to be:

Oh Marshall...Have I learned a lot from you...including how to ask a
question. I will, from now on, never forget the importance of the
formatting of a field(s) when asking a question because, yes, with
that change to the code it now works!!!!!!!! And I should have caught
the word FORMAT and grasped what I was looking at. I am still testing
and trying to think up ways to "break" it but so far so good!!

Is it the double quotes that describe cboDept as text rather than a
number? Also, for the purposes of my db I have only included 2 depts
at this point which is why I used text instead of a number field as an
index. I am guessing that over time I would probably be better off
creating a numeic index such as DeptID (number), and DeptName
(text)...good and proper thinking?

At this point - if you have time (and I am grateful for the inordinate
amount of time you have already spent with me) - I would love to hear
your take on my db design. On the other hand - if you don't have time
- I absolutely understand.

Right now, now that I am beginning to really understand what is going
on in your code I am going to study it for awhile because I am sure I
can use the same concept elsewhere.

Thank you very much,

Tim

 

[Marshall Barton]

Oh Marshall...Have I learned a lot from you...including how to ask a
question. I will, from now on, never forget the importance of the
formatting of a field(s) when asking a question because, yes, with
that change to the code it now works!!!!!!!!

Hooray ;-)

And I should have caught
the word FORMAT and grasped what I was looking at.

Be careful with that word. The Format property has very
little or nothing to do with its value. The really
important thing is the DataType (and for the Number type,
the FieldSize).

I used the Format function on the date field to make sure
that Access would be able to interpret the resulting literal
value independently of the Windows Regional date settings
for the order of the year month and day and the separator
between them. If one of your users decided to set his
machine for the date format (d.m.y) as most of the rest of
the world does then, without the format function, you would
get an error about a number with two decimal points. Even
the standard US style dates m/d/y date would be treated as
two divisions so Access requires the date to be enclosed in
# signs. Putting all that confusion together results in the
formatting I used. The reason we have to go through all
that is because we are concatenating the values in a string
and we don't want Access to guess at the date format.
Numbers are unambiguous so they do not require anything
special. OTOH, a text value concatenated into our SQL
statement would be strange syntax unless it is enclosed in
quotes.

You can see the result of all that conversion and
concatenation by placing a break point on the Execute line
and inspecting the strSQL variable. Eventually you will
learn enough about queries and SQL statements to spot errors
caused by missing delimiters, missing spaces and .

I am still testing
and trying to think up ways to "break" it but so far so good!!

Is it the double quotes that describe cboDept as text rather than a
number?

No. The value of the combo box is determined by the data
type of the bound column field in its row source. The
quotes are required because a literal text value is not
recognizable unless it is in quotes.

Also, for the purposes of my db I have only included 2 depts
at this point which is why I used text instead of a number field as an
index. I am guessing that over time I would probably be better off
creating a numeic index such as DeptID (number), and DeptName
(text)...good and proper thinking?

If a DeptName might be changed in the future, then I think
that's a good idea. OTOH, a static name is sufficient and
an extra number id field would just be an unnecessary extra
mechanism that Murphy might want to utilized in his never
ending quest for ways to cause. The debate over this issue
is a near religious argument. Personally, I usually use the
id number field because I don't trust much of anything to be
static.

At this point - if you have time (and I am grateful for the inordinate
amount of time you have already spent with me) - I would love to hear
your take on my db design. On the other hand - if you don't have time
- I absolutely understand.

I think I have already mentioned the table design points
that I had any questions about.

The concept of using an unbound main form for users to
specify the filter values and using the link master/child
properties to apply the filter to a bound subform is one I
had not really considered before. (Normally a subform is
used when you want to relate records from two tables.)
However, I suspect that it may very well be easier and more
reliable than using the Filter property on a single form, so
I am going to log that away as a potentially good idea.

Right now, now that I am beginning to really understand what is going
on in your code I am going to study it for awhile because I am sure I
can use the same concept elsewhere.

The concept of using an SQL statement to add a record to a
table is very useful, but only in limited situations. The
"normal" way to add a record is to let users do it through a
bound form.

 

[tbrogdon]

The concept of using an unbound main form for users to
specify the filter values and using the link master/child
properties to apply the filter to a bound subform is one I
had not really considered before.  (Normally a subform is
used when you want to relate records from two tables.)
However, I suspect that it may very well be easier and more
reliable than using the Filter property on a single form, so
I am going to log that away as a potentially good idea.

I will let you know how it works out. Thank you for going outside of
the box with me!

 

[tbrogdon]

Hi Marshall,

The function you helped me with is working great. It accomplishes
exactly what I need.

I do have a twist however: sfmProdOp is on page(0) of a tab control on
frmProduction. On page(1) of the tab control, I have frmEmpProd which
is bound to tblEmployeeProduction which consists of the following
fields: ProductionDate, Dept, Shift, EmployeeID, JobFunctionID,
HoursAssembly, HoursMachine. The purpose of tblEmployeeProduction is
to capture the number of hours that employees work either in or out of
their specified default job function.

Anyway, when I input data in our unbound controls on frmProd,
sfmEmpProd on page(1) doesn't respond like sfmProdOp. What happens
currently is that the entire tblEmployeeProduction is visible in
sfmEmpProd.

When I created page(1) of the tab control, I used the subform option
from the toolbox and let the wizard walk me through. Since frmProd is
unbound I received a message about not being able to link the two
forms so I selected the edge of sfmEmpProd and manually typed in the
identical linkage in the LinkMaster/Child property fields just as we
did with sfmProdOp but I don't really have any confirmation that the
the link did or did not work other than the records don't reflect a
linkage like they do on the other subform. All of the controls on
sfmEmpProd are the same as sfmProdOp (i.e., txtProductionDate,
cboDept, cboShift - formats are all the same as well - no hidden text
that should be a number or vice versa) and their control sources are
identical in name and format as well (i.e., ProductionDate, Dept,
Shift).

Here is the code that works for SaveIfNew():

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

If IsNull(txtProductionDate) Or IsNull(cboDept) _
Or IsNull(cboShift) _
Then Exit Function

Set db = CurrentDb()

strSQL = "INSERT INTO Production" _
& "(ProductionDate, Dept, Shift)" _
& "VALUES(" _
& Format(txtProductionDate, "\#yyyy-m-d\#") _
& ",""" & cboDept & """," & cboShift & ")"

db.Execute strSQL

End Function

What do you think? Do I need to have sfmEmpProd requery? Do I need to
refer to the controls differently for each subform somehow in the sql
in the INSERT INTO clause?

Thank you again,

Tim

 

[Marshall Barton]

The function you helped me with is working great. It accomplishes
exactly what I need.

I do have a twist however: sfmProdOp is on page(0) of a tab control on
frmProduction. On page(1) of the tab control, I have frmEmpProd which
is bound to tblEmployeeProduction which consists of the following
fields: ProductionDate, Dept, Shift, EmployeeID, JobFunctionID,
HoursAssembly, HoursMachine. The purpose of tblEmployeeProduction is
to capture the number of hours that employees work either in or out of
their specified default job function.

Anyway, when I input data in our unbound controls on frmProd,
sfmEmpProd on page(1) doesn't respond like sfmProdOp. What happens
currently is that the entire tblEmployeeProduction is visible in
sfmEmpProd.

When I created page(1) of the tab control, I used the subform option
from the toolbox and let the wizard walk me through. Since frmProd is
unbound I received a message about not being able to link the two
forms so I selected the edge of sfmEmpProd and manually typed in the
identical linkage in the LinkMaster/Child property fields just as we
did with sfmProdOp but I don't really have any confirmation that the
the link did or did not work other than the records don't reflect a
linkage like they do on the other subform. All of the controls on
sfmEmpProd are the same as sfmProdOp (i.e., txtProductionDate,
cboDept, cboShift - formats are all the same as well - no hidden text
that should be a number or vice versa) and their control sources are
identical in name and format as well (i.e., ProductionDate, Dept,
Shift).

I don't think the save function has anything to do do with
linking the subform.

If tblEmployeeProduction contains the fields ProductionDate,
Dept and Shift the same as tblProdOp, then the two subform
controls' Link Master/Child properties should be identical.

Don't forget the the link master property refers to
**controls** on the main form and the link child property
refers to **fields** in the subform's record source
table/query.

I'm not sure what else you need, but if you want sfmEmpProd
to display records for a single employee, then you'll need
an additional mechanism to identify the employee.