Auto populate a form from an open form

[far-mer]

Hi

I have a main form with customer names and addresses and would like to
add a control button on this form which opens up another form which is
used for recording telephone calls on. What I have been trying to do
is auto populate this call log form with information from the main
form ie name and address?


Any thoughts


Thanks


Chris

 

[Jarrod Shumaker]

Depending on your scenario, have you considered using tabs to break out
seperate steps in your process?

Do you have two databases? One for the customer names and such and another
for the phone call info?

If not, a tabbed form should work for you, just set the fields in the
"second" (phone call) tab to equal the contents of the first tab, or just
replicate that field from the first tab.

 

[Al Campagna]

Chris,
You should use a subform, related to the main form via some unique key field like
ClientID.
Your tblClients table (the ONE) related to tblCalls table (the MANY).
When a client calls, go to that client record and add another record to the Call
subform... (date, starttime, stoptime, subject... etc)
You don't want to save the Customer name and address in each call record... just
relate the calls to the Client via the ClientID.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[far-mer]

Al thanks

My understanding is that a sub-form would have to embedded into the main form?
I was hoping to find a solution that would enable the form to pop-up?

Thanks for your response

Chris

Chris,
You should use a subform, related to the main form via some unique key field like
ClientID.
Your tblClients table (the ONE) related to tblCalls table (the MANY).
When a client calls, go to that client record and add another record to the Call
subform... (date, starttime, stoptime, subject... etc)
You don't want to save the Customer name and address in each call record... just
relate the calls to the Client via the ClientID.

Hi

[quoted text clipped - 9 lines]

Chris

 

[far-mer]

Jarrod

Thanks

No I have one database but two tables, one with the main client info and the
other with call data. What I was hoping to do at a later stage was to have a
button on each record which when clicked popped up with all of the phone call
data for that client. I have a third form which shows all of the outstanding
calls which have not been dealt with, this refreshes every few mins.

Thanks

Chris

Depending on your scenario, have you considered using tabs to break out
seperate steps in your process?

Do you have two databases? One for the customer names and such and another
for the phone call info?

If not, a tabbed form should work for you, just set the fields in the
"second" (phone call) tab to equal the contents of the first tab, or just
replicate that field from the first tab.

Hi

[quoted text clipped - 9 lines]

Chris

 

[Al Campagna]

I would suppose you could do that, although I've never done that. The query behind the
pop up would use some value from the open main form to filter the records delivered.
Have you considered Tab pages for multiple subforms... if space on the main form is an
issue?
That's the "usual" method...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al thanks

My understanding is that a sub-form would have to embedded into the main form?
I was hoping to find a solution that would enable the form to pop-up?

Thanks for your response

Chris

Chris,
You should use a subform, related to the main form via some unique key field like
ClientID.
Your tblClients table (the ONE) related to tblCalls table (the MANY).
When a client calls, go to that client record and add another record to the Call
subform... (date, starttime, stoptime, subject... etc)
You don't want to save the Customer name and address in each call record... just
relate the calls to the Client via the ClientID.

Hi

[quoted text clipped - 9 lines]

Chris

 

[far-mer via AccessMonster.com]

Al

Yes my main form consists of a tabbed page, however, (having played with this
most of the night) because I'm using a Now() for the time the call was taken
so that when the user logs the phone call it is given a time stamp - access
then creates the next entry below with the same time stamp, which causes a
problem when you have to make two entries as the old time stamp remains.

I managed to transfer the recordID from the form where the command button was
located on to the call record form by the following:

Default value [Forms]![New input screen]![Job Number]

I'm now trying an unbound form to stop the second record appearing below with
a time stamp.

Thanks for your response

Chris

 

[Al Campagna]

Farmer,
Please... don't delete the previous posts from the "thread." It's helpful to see the
"flow" of the problem determination. Just let the posts "chain."

Well, I'm losing you on this... I can not figure out what you're trying to accomplish,
that a normal Client Main form, and One to Many realtionship with a Calls subform won't
do. There also may be some "language" or "terminology" problems that are causing my
confusion.
You should (at least) get the application to work in the most basic manner... and
then... if you must... expirement with some more complicated solution.

I can only suggest how I would do it...

When a client calls, you find the correct client record (Main), and add a new record to
the Call (subform).
Because Client records (ClientID-Autonumber) are related (one to many/refrential
integrity/cascades) to Calls (ClientID-Long), and are linked on the form via Master/Child
fields...
whenever you add a new call record, the ClientID in Calls is automatically updated to the
related ClientID.
CallStart should have a Default Value of Now()... (which you can change if needed)
so... you just have fill in the "other" call info (Subject, CallEnd, etc..)
CallStart should be Indexed, but "Allow Duplicates" should be true. If not set that
way, you won't be able to enter 2 calls with the same CallStart.

You appear to say that when you create a new call record (say... 3/11/07 @ 9:00am),
something creates another record with that same date and time??
That must be something you coded in, and I can't respond to that without more detailed
information on your code.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[far-mer via AccessMonster.com]

Al

Thanks for your help, I think I have cracked it. I have created an unbound
form that is called from the main page via a command button, it picks up the
ClientID in the process. When it is saved its uses the following code to
write the call information into the calls table:

Private Sub Command19_Click()

On Error GoTo Err_Command19_Click

Dim rstOrder As ADODB.Recordset

Set rstOrder = New ADODB.Recordset

rstOrder.Open "Calls", CurrentProject.Connection, adOpenStatic,
adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("Priority") = frmpriority
.Fields("Job Number") = frmjobnumber
.Fields("Date and time of call") = frmdatetime
.Fields("Comment") = frmcomment
.Fields("Staff taking call") = frmstafftakingcall
.Fields("Call for") = frmcallfor
.Update
Command22_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

' DoCmd.Close

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub

The call information is displayed on another form like an open call log, each
entry is colour coded based on the prority set from the call form. Each call
is displayed on a different line and alongside each entry is an "archive"
command button which should put a date in the call closed field of the calls
table - this is being done by an update query. However, when you click on it
it updates all of the records in the table not the one, I can't get it to
only update the record it is located on. For example the request I would
give is "update the record I am on with todays date and time". The call log
form is set to refresh every five minutes and to ignore any records that have
an archived date and time.

Hope this makes sense?

Thanks again for your help

Chris

Farmer,
Please... don't delete the previous posts from the "thread." It's helpful to see the
"flow" of the problem determination. Just let the posts "chain."

Well, I'm losing you on this... I can not figure out what you're trying to accomplish,
that a normal Client Main form, and One to Many realtionship with a Calls subform won't
do. There also may be some "language" or "terminology" problems that are causing my
confusion.
You should (at least) get the application to work in the most basic manner... and
then... if you must... expirement with some more complicated solution.

I can only suggest how I would do it...

When a client calls, you find the correct client record (Main), and add a new record to
the Call (subform).
Because Client records (ClientID-Autonumber) are related (one to many/refrential
integrity/cascades) to Calls (ClientID-Long), and are linked on the form via Master/Child
fields...
whenever you add a new call record, the ClientID in Calls is automatically updated to the
related ClientID.
CallStart should have a Default Value of Now()... (which you can change if needed)
so... you just have fill in the "other" call info (Subject, CallEnd, etc..)
CallStart should be Indexed, but "Allow Duplicates" should be true. If not set that
way, you won't be able to enter 2 calls with the same CallStart.

You appear to say that when you create a new call record (say... 3/11/07 @ 9:00am),
something creates another record with that same date and time??
That must be something you coded in, and I can't respond to that without more detailed
information on your code.

Al

[quoted text clipped - 15 lines]

Chris