We started on the right track but I think we derailed somewhere. I do not
want to link the tables. I simply want to take the owner information from
the PRA TEMP and copy it into the owner information for each of the selected
records in EVMASTER when the user leaves the form.
The original database (DbaseIII) had the EVMASTER table with all of the item
information including the owner's last name, first name, etc. I converted it
to Access about seven years ago and have been making improvements ever since.
I know the database design is flawed, but to go back now and convert all of
the owner information on EVMASTER to a table for over 100,000 records just
isn't worth it. I created the PRA TEMP to collect the owner information when
items are returned so it could be included on the printed report which has
the owner information from PRA TEMP as well as the selected items from
EVMASTER. As it stands now, the user then has to go into the EVMASTER table
and re-enter the owner information for each record that was included on the
report. I just want the owner information to be copied to each record
automatically. I was thinking that I could use an Iif statement where PRA
CHECKBOX = True, then set the value for the owner information on EVMASTER to
the owner information on PRA TEMP for these selected records.
I am very impressed that you have been able to follow me this far....I'm
getting very confused even though I've been working with it for years.
:
first, you need a field to link the tables together. Why
does the temporary table not have the ID field? That would
be the best to use to link them for updating.
"The temp table allows the user to enter the owner
information one time instead of for each EVMASTER record."
This makes me think that your database is not normalized.
You should have owner information stored in just one place
-- not on several records. Then you would use OwnerID to
show that information whenever you need to see it.
Have an awesome day
Warm Regards,
Crystal
MVP Microsoft Access
strive4peace2006 at yahoo.com
I would like to update EVMASTER from PRA TEMP but only on those records where
PRA_CHECKBOX = True. When the user enters the form and checks records to
select, a form is created that these records will be returned to the owner
designated on the second form. The temp table allows the user to enter the
owner information one time instead of for each EVMASTER record. I just can't
figure out how to update these fields on EVMASTER from PRA TEMP on just the
selected records.
:
What is the purpose of the temporary table? Why not change
the actual records?
If the temp table does not have the ID field, how are you
ensuring that you are changing the correct records?
Just as you are using SQL to Update pra_checkbox, you can
Update EVMASTER from PRA TEMP
Have an awesome day
Warm Regards,
Crystal
MVP Microsoft Access
strive4peace2006 at yahoo.com
Mackster66 wrote:
There can be more than one record checked, and that is one of the problems I
couldn't find a solution for on my own. Each EVMASTER record has an
autonumber ID field. The PRA TEMP table just has the OWNER LAST/FIRST/ADDR
fields. Another little wrench in the works is that I have the following code
in the PRA CHECKLIST form to clear the checkboxes when the CLOSE FORM command
button is clicked.
--------------------------------------------
Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click
Dim strSql As String
strSql = "UPDATE [evmaster] SET [pra_checkbox] = False WHERE
[pra_checkbox] = True;"
DBEngine(0)(0).Execute strSql, dbFailOnError
DoCmd.CLOSE
Exit_Close_Form_Click:
Exit Sub
Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click
End Sub
---------------------------------------
:
just organized your notes a bit to make sure I see what is
happening
1. 'PRA CHECKLIST'
based on query 'PRA FROM EVMASTER'
based on table 'EVMASTER'
--> 'PRA CHECKBOX' to select
records of interest
2. form 'PRA OWNER INFO'
fields --> 'OWNER LAST', 'OWNER FIRST', and 'OWNER ADDR'
based on table 'PRA TEMP'
it appears that you want values from table 'PRA TEMP' to be
written to table 'EVMASTER'
If you use the form CLOSE event, you will not be able to get
data from the controls -- you can use the form UNLOAD event
for that.
'------------------------
with forms("PRA CHECKLIST")
.OwnerLast_controlname = me.OwnerLast_controlname
.OwnerFirst_controlname = me.OwnerFirst_controlname
.OwnerAddr_controlname = me.OwnerAddr_controlname
end with
'make changes show up right away
DoEvents
'------------------------
question: will more than one record be checked in PRA
CHECKBOX? If so, this will not be the best way since this
will just handle one record. If you are changing more
records, an UPDATE QUERY would be needed -- and that could
be on the CLOSE event.
Is there an ID field to identify how to match the records?
Have an awesome day
Warm Regards,
Crystal
MVP Microsoft Access
strive4peace2006 at yahoo.com
Mackster66 wrote:
MS Access 2003. I have a form 'PRA CHECKLIST' based on query 'PRA FROM
EVMASTER' based on table 'EVMASTER'. This form brings up records for a
specified value and requires the user to click 'PRA CHECKBOX' to select
records of interest . After the checkboxes are selected, the user clicks a
button 'ENTER OWNER INFO' which takes them to next form 'PRA OWNER INFO'.
This form has fields 'OWNER LAST', 'OWNER FIRST', and 'OWNER ADDR'. These
fields are based on table 'PRA TEMP'. The user is then directed to a report
that prints out the records from the first form and the owner information
from the second form. What I am trying to do is have an event controlled by
the second form's 'CLOSE' button that enters the owner information from PRA
TEMP into the fields on 'PRA FROM EVMASTER' in the records where 'PRA
CHECKBOX' = true.
I know it is against good database design to have such redundant information
stored in tables, but please understand that this database has evolved over
the last fifteen years and some of the design would be too difficult for me
to change. Can you suggest some VBA code to accomplish this task?
