Primary Keys

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI All,

I wonder if anyone can help. I have tried looking for the answer through the
help option and in many of the books I have, but to no avail..

I have several tables all relating to client information. I have split them
into tables as it is a lot of information. As each record will only relate
once to the main table, I have chosen a One to One Relationship.

My problem is this, the Reference for each case is the same and what I would
like to do if for the agents to enter the reference in once, in the main
table, and then have it automatically create the other records in the related
tables using the Reference as the indentifier.

Is this possible?

Many thanks

Lee
 
Set your tables up like this:

TblClient
ClientID
ClientFName
ClientLName
etc

TBlClientAddress
ClientAddressID
ClientID
Address1
Address2
City
State
Zip

TblClientContact
ClientContactID
ClientID
HomePhone
OfficePhone
CellPhone
Fax
EmailAddress

Base your main form on TblClient. Make subforms on your main form for
TblClientAddress and TblClientContact. For both subforms, make sure the
LinkMaster and LinkChild properties are set to ClientID. When you open your
main form and enter a new client, a unique ClientID (autonumber) will be
automatically created for that client. As soon as you enter that client's
address in the address subform, that same ClientID will automatically be
saved with the address record. Likewise, when you enter any contact data for
a client, that client's ClientID will be saved in the contact record. In
summary, if you set up a form/subform, Access will automatically create the
Reference in the form of an autonumber for you.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Hi Steve

Thanks for the info. Only trouble is, the way we want to access the
information means that we have seperate screens for each section, such as
business information, and they way you have suggested means we will have 8
sub forms on one form, this would look a little unsightly and may be
confusing. also, from what you have stated, it would only create a link once
you have netered info in that subform. What we would like is for it to
autocreate a record even if there is no infro to put in.

I hope this makes sense! You can tell I am new at this sort of thing!

Many thanks

Lee
 
Thanks for the info. Only trouble is, the way we want to access the
information means that we have seperate screens for each section, such as
business information, and they way you have suggested means we will have 8
sub forms on one form, this would look a little unsightly and may be
confusing. also, from what you have stated, it would only create a link once
you have netered info in that subform. What we would like is for it to
autocreate a record even if there is no infro to put in.
Click to expand...

Could you describe these "sections"? One to one relationships are *quite*
uncommon (not wrong, necessarily, but right only in some very unusual
circumstances).

It is *NEVER* necessary to create empty placeholder records, though - whether
with one to one or one to many relationships. They have a nasty way of never
getting filled in, and with proper form design they present no advantages over
an empty subform. You can have as many subforms as you like, each on a
separate tab page, so only one will be visible at a time, and any of them can
be easily selected when needed. A new record will be created with the link
simply by selecting the subform and starting to enter data.

John W. Vinson [MVP]
 
Hi Lee,

You only need one subform on the main form. You would have a combobox,
listbox, a group of option buttons or some other means to change the source
object of the subform. You could select whichever section you wished to view
in the subform. I agree with John 100% that it is a bad idea to autocreate a
record even if there is no infro to put in. If you want, I can set this all
up for you, import your existing data into the database and get your
database up and running for a very reasonable fee.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
If you want, I can set this all
up for you, import your existing data into the database and get your
database up and running for a very reasonable fee.
Click to expand...

Steve, trolling the newsgroups for work is unethical. You know that.

Lee, be aware that the vast bulk of the people here donate their time for free
to help people. Steve is an unfortunate exception to that custom.

John W. Vinson [MVP]
 
You could use the After Insert event to create the required foreign records
with VBA code, something like (the property SQLServer is used to determine
if we are working against a SQL-Server as the backend; this is also why the
option dbSeeChanges is used) :


Private Sub Form_AfterInsert()

' Création des fiches associées au premier conteneur:
OffreService_CreationFiches Me!NoOffreService

Me.Refresh ' Réactualise également les deux sous-formes.

' Ajuste les modes Insert et Deletion pour les deux sous-formes.
Call FrameQED_Ajustements

End Sub


Function OffreService_CreationFiches(ByVal NoOffreService As Long) As
Boolean

OffreService_CreationFiches = False ' En cas d'exit prématurée.
On Error GoTo label_erreur

If (NoOffreService = 0) Then
MsgBox "Erreur interne: OffreService_CreationFiches(), #1."
Exit Function
End If

Dim db As DAO.Database
Dim wk As DAO.Workspace
Dim tr As Boolean
Dim rs As DAO.Recordset

Set wk = DBEngine.Workspaces(0)
Set db = wk.Databases(0)

wk.BeginTrans
tr = True

Dim IdConteneur As Long

' Création de la fiche Conteneur suivie de celles des autres fiches
associées.
Set rs = db.OpenRecordset("Conteneurs", dbOpenDynaset, dbAppendOnly Or
dbSeeChanges)
rs.AddNew
rs!No = 1
rs!NoOffreService = NoOffreService
rs!DateService = DLookup("DateArriveePrevue", "OffresService",
"[NoOffreService]=" & NoOffreService)

#If SQLServer = 1 Then
rs.Update
rs.Bookmark = rs.LastModified
IdConteneur = rs!IdConteneur
#Else
IdConteneur = rs!IdConteneur
rs.Update
#End If

rs.Close

' Création de l'Avis de déplacement (1 par Conteneur).
Set rs = db.OpenRecordset("AvisDeplacement", dbOpenDynaset, dbAppendOnly
Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur

Dim IdEmploye
IdEmploye = DLookup("IdEmploye", "Employes", "DefautAD=True")

If (Not IsNull(IdEmploye)) Then
rs!IdEmploye_Resp = IdEmploye
End If

IdEmploye = DLookup("IdEmploye", "Employes", "DefautOS=True")

If (Not IsNull(IdEmploye)) Then
rs!IdEmploye_Sign = IdEmploye
End If

rs.Update
rs.Close

' Création de l'Accusé de réception (1 par Conteneur).
Set rs = db.OpenRecordset("AccusesReception", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur
rs!NoCentre = 0 ' Centre non-défini par défaut.
rs.Update
rs.Close

' Création du Rapport de fumigation (1 par Conteneur).
Set rs = db.OpenRecordset("RapportsFumigation", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur

IdEmploye = DLookup("IdEmploye", "Employes", "DefautRappF=True")

If (Not IsNull(IdEmploye)) Then
rs!IdEmploye = IdEmploye
End If

rs.Update
rs.Close

' Création de l'Avis de relâchement (1 par Conteneur).
Set rs = db.OpenRecordset("AvisRelachement", dbOpenDynaset, dbAppendOnly
Or dbSeeChanges)
rs.AddNew
rs!IdConteneur = IdConteneur
rs.Update
rs.Close

tr = False
wk.CommitTrans
OffreService_CreationFiches = True

label_exit:
Exit Function

label_erreur:
MsgBox Err.Number & " - " & Err.description
If (tr) Then wk.Rollback

MsgBox "La première fiche « Conteneur » associée à cette nouvelle offre
de service n'a pu être créée.", vbCritical
Resume label_exit

End Function


In this code, the creation of records by VBA code have been enclosed in a
transaction. However, to be truly OK, the creation of the initial record
(OffreService) should also be part of the transaction but this is impossible
to do with a bound form under Access. If you want to, you could correct
this by forcing the user to click on a button for the creation of a new set
of records (and deactivate the possibility of insertion on the form) and
enclose all these in a single transaction.
 
--
To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Beware of his guy !!
Before you intend to do business with him, look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R
 
Thanks for all the help everyone.

I agree with the statement made about NOT creating blank records and this
has clarified a point for me. I fI am not going to create a record for any
table that will have no information, I do not need to worry about having it
populate the Reference. I think a series of Subforms is a good idea and I can
put them in each section of my database.

Thank you again for all your help!

Lee
 
Back
Top