Copying some field entries to another table when they are displayed on a form.

[andy.cowman]

Hello

I have been trying to work this out for a while, I am sure I have done
something like it before but just can't seem to get it now.

Basically I have a table containing client information and another
containing transactions infomation. Both have an address field becuase
the transaction may not be about the client's corrsaspondance address.
Sometimes it is though and so I wanted on my form that display the
Client Info to have a button that click and it copies that clients
address details to the other table. At the moment the button has the
following code in VB

Dim stDocName As String
Forms("frmClientDetails").Refresh
stDocName = "qryCopyAddressDetails"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Forms("frmClientDetails").Refresh


and it calls an append query which will then ask for the ClientID
number which is unique and will then copy that persons corraspondance
address into a new record in the transaction table. The form then uses
tabs to move onto entering the data for the transaction relating to
that address.

This works fine but ideally I do not want to have enter the Client ID.
I would like the button to use pick up the client ID from the form and
automatically use that. It seems like it should be quite simple but I
can't do it?

Please advise!!

many thanks

Andy

 

[scubadiver]

I feel there is a design issue here somewhere but can't quite put my finger
on it. Your DB isn't quite normalised because there shouldn't be a need to
store the same address in more than one table.

Having said that I can see why you want to do it the way you are. In the
append query, don't you have something like

[forms]![form name]![field name]

in the criteria box. This should run the query with the current ClientID
number.

 

[Falty]

Hello

I have been trying to work this out for a while, I am sure I have done
something like it before but just can't seem to get it now.

Basically I have a table containing client information and another
containing transactions infomation. Both have an address field becuase
the transaction may not be about the client's corrsaspondance address.
Sometimes it is though and so I wanted on my form that display the
Client Info to have a button that click and it copies that clients
address details to the other table. At the moment the button has the
following code in VB

Dim stDocName As String
Forms("frmClientDetails").Refresh
stDocName = "qryCopyAddressDetails"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Forms("frmClientDetails").Refresh


and it calls an append query which will then ask for the ClientID
number which is unique and will then copy that persons corraspondance
address into a new record in the transaction table. The form then uses
tabs to move onto entering the data for the transaction relating to
that address.

This works fine but ideally I do not want to have enter the Client ID.
I would like the button to use pick up the client ID from the form and
automatically use that. It seems like it should be quite simple but I
can't do it?

Please advise!!

many thanks

Andy

In the query where you have a parameter query which asks for id simply
replace it with a reference to the control on the form. it might look
something like this

Forms!frmClientDetails!ClientID

Let me know how you get on

Falty

 

[andy.cowman]

It seems like a design issue I agree but I could not for the life of
my work out how else to do it. There is no pattern to when the address
may be used twice, it is a purely indivudal thing. Most of the time
the transactions are for the sale of a house that is not the persons
corraspondance address and each client can have many transactions over
time. Sometimes it may be that their corraspsonace address is property
of the transaction so the address would be used for two seperate
things. After completion the corraspondance address may well be
updated to their new one.... so I cant see any way of combining the
data!!

Thanks for the tips, I shall give them a go. Should speed things up
nicely!

Andy

 

[Falty]

You could always try having the addresses in a different table and linking
them via a key to the clients table. You could then simply have the the
addresses in a subform. could be one possible solution but would require a
fair bit of admin work.

 

[andy.cowman]

Hmm that doesnt seem to work. Is there a way that VB can supply the
answer to the querys request for the ClientID number from the record
displayed on the form?

Thanks

Andy

 

[andy.cowman]

So each address is linked to the client (so they can have as many as
they want) and then one inparticular is marked as their corraspondance
address? Thats a good idea but could lead to an unwiedly subform if
someone happend to get through their houses pretty quickly! Also then
there would be the problem of generating the report the set address
which would need a similar set up to the one I am using now!

I am sure what I am trying to do is not that hard, I am just being
very thick

Andy

 

[Falty]

I see how that idea might be a bit bad now. Right well is this client id
field on a form or a subform?

forms!NameOfForm!NameOfControl

should do the trick in the criteria for ClientID on the query.

If it is on a subform it will be something like

forms!NameOfForm!NameOfSubform!NameOfControl

this is where
NameOfForm = name of your form
NameOfSubform = name of your subform
NameOfControl = the name of the control that has ClientID

the form will have to be open on the selected client for this query to find
the control. I have used this many times and had no problems

 

[andy.cowman]

That makes more sense yes, think I see where I was going wrong. I
shall have another play with it tonight when I get home and post up
how I get on

Thanks again