How do I select several records and save them as a new record

[JMCx2]

I need to create visit records that track who visited what work site. The
visitors' information already exists as records in my databse. I need to
select several existing records (names, numbers, etc) from tblpersonnel, add
the visit specific information and save it all as a new record. I have
created a form based on tblvisits that contains the visit fields and put in a
subforms that contains all the personnel records from tblpersonnell. This is
as far as I have gotten.

 

[Larry Daugherty]

Hi,

You haven't provided sufficient information that we could infer
entirely what you want to achieve and how far along you really are
toward getting there. Some notes:

The visible information you want to create can be created any time you
need it. To make it available to others you can create and publish a
Report. To create and display such information in an *unbound* form
is also a reasonable thing to do.

Creating it and saving it in your Access application is a *bad* idea.
A record included in the summary can be amended. Then your summary is
inaccurate. There are other compelling reasons why you should not
save it back in the database.

HTH

 

[Tim Ferguson]

I need to create visit records that track who visited what work site.

create table visits (
visitorId integer not null
foreign key references Personnel,
dateOfVisit datetime not null,
placeToVisit integer not null
foreign key references Places,
timeSpentInMinutes Integer

constraint PK primary key (visitorID, dateOfVisit, placeToVisit)
)

The visitors' information already exists as records in my databse. I
need to select several existing records (names, numbers, etc) from
tblpersonnel, add the visit specific information and save it all as a
new record.

Sounds like you need a form with a listbox set to multiselect, a way of
picking the date and the place visited. The "Save" command button would
simply run a series of INSERT commands for each of Visitor to create the
row in the Visits table.

I have created a form based on tblvisits that contains the
visit fields and put in a subforms that contains all the personnel
records from tblpersonnell. This is as far as I have gotten.

That's probably another way to do it. The Right Answer depends, as
always, on your users. How do they carry out this process at the moment?
Start with a calendar, pick the place to visit, then write in the
visitors? Or start with a visitor and tick off each place he or she went
during the week? Etc etc. It's called systems analysis, and you should
have done lots and lots of it before touching a key on your PC...

Hope that helps


Tim F

 

[JMCx2]

Tim,

Thank you for your response. I did not describe the process accurately. This
process also serves as a visit notification.
The user needs to create a new visit record, enter the the visit details,
then scroll through and select the visitors, (all visitors will be in the
database already) put those visitors (several fields) on the form, and print
the report. I want to save these visits with the selected personnel records
as a new visit record.

 

[JMCx2]

Larry,

Thank you for replying. Ideally I would like the user to be able view all
the previous visits in form view with the selected visitor records on the
form. If that is not agood idea, being able to view the previous visits as
saved reports might be a good option also. How do I make it easy for the user
to view these reports?

 

[Larry Daugherty]

Looking at those previous visits in form view is a very common thing
to do. You can probably get there using "Filter by Form". Look in
Help for guidance and try it out.

Trying to view saved reports is probably not a good thing to do.
However, you could easily create a new report that will show the
things you're interested in about just the current visitor.

I've done just that in an application for medical transcriptionists.
They often have difficulties with accents on the tapes. I put a
button on their data entry form that opens a report that displays
items of interest to the transcriber about the current patient's prior
visits, if any.

HTH

 

[Tim Ferguson]

etc

The user needs to create a new visit record, enter the the visit
details, then scroll through and select the visitors, (all visitors
will be in the database already) put those visitors (several fields)

This "several fields" stuff is a Major Warning Sign. It really really
really sounds as though they should be several records in a
WhoDidTheVisits table, which would be related to the Personnel table and
the Visits table.

on the form, and print the report. I want to save these visits with
the selected personnel records as a new visit record.

That the last sentence does not make sense underlines the fact that you
need to get the db design right first. The UI then falls quite easily and
naturally once you are dealing with the correct models of the real life
entities. I can imagine a couple of ways of presenting it to the users,
but only you will know the best way. But it all depends on getting the
tables correct in the first place.

Hope that helps


Tim F