Combo box to fill in multiple fields

[Triacona]

Dear All,

Thanks for a great forum.

I have a db that loggs calls and there are frequent assistants.
So instead of having to type in all these assistants details manually every time I created a table [green]FreqAssistants.[/green]

The table [green]FreqAssistants.[/green] has the following fields.

AssistantCode
Name
Surname
Title
TelephoneNumber
E-mail


I have a form [maroon]CallsLogged[/maroon]
It is linked to a query [blue]CallsLoggedQ[/blue]
it has the following relevant fields

AssistantCode
Name
Surname
Title
TelephoneNumber
E-mail
AssignedReference

I have a combo box [purple]cmbCmbChooseAssistant[/purple]

Control Source: AssistantCode
Input Mask:
Row Source Type: Table/Query
Row Source: SELECT FreqAssistants.AssistantCode, FreqAssistants.Name, FreqAssistants.Surname, FreqAssistants.Title, FreqAssistants.TelephoneNumber, FreqAssistants.[E-mail] FROM FreqAssistants;
Bound Column:1
Limit To List: Yes
Auto Expand: Yes

I wonder if there is a way to have more than 1 control source.
For example, AssistantCode, AssistantName etc.
So this would then from FreqAssistants table fill in all the relavant fields (as above) in the form CallsLogged.

Any help would be greatly appreciated.

Thank you

Thank you,

Kind regards

Triacona

 

[PHV]

Use the Column property of the ComboBox to populate your controls.
You may do it in the AfterUpdate event procedure of the combo.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Triacona,
Are you sure you want to store the same information over and over and over in your tables?

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear All,

Thank you cery much for the replies.

PHV I still kind of a noob at coding controls to fill out tables,
how would I go about this? Thanks for your help...

Are you sure you want to store the same information over and over and over in your tables?

If I were not going to do it the way I described...
The only other way I can think of is to only have the

AssistantCode and reference in the Assistant table

and have the AssistantCode and the rest of the fields in the FreqAssistant table.

I'll give that a try as well...

Thank you both for all your help it is much appreciated.

Thank you,

Kind regards

Triacona

 

[dhookom]

The solution that PHV is suggesting for the AfterUpdate of the combobox might look like:

Me.txtName = Me.cmbCmbChooseAssistant.Column(1)
Me.txtSurname = Me.cmbCmbChooseAssistant.Column(2)
Me.txtTitle = Me.cmbCmbChooseAssistant.Column(3)
Me.txtTelephoneNumber = Me.cmbCmbChooseAssistant.Column(4)
Me.txtEmail = Me.cmbCmbChooseAssistant.Column(5)

Your code would depend on the names of your controls and the appropriate column numbers beginning with 0.

BTW: "Name" is not a good name for anything since everything in Access has a "Name" property.


Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear All,

Thank you so much for you help

@Dhookom
I have though about what you said and it makes sense...
I have therefore restructured...
I now have the following tables.

[green]

Logger
[highlight](primary key = RegistrarCode)[/highlight]
[red](One Logger to Many Loggers)[/red]

Loggers
[blue]Linked to[/blue]
Logger[black] (RegistrarRef)to [/black] [highlight]RegistrarCode[/highlight]

[highlight](primary key = AssignedReference)[/highlight]
[blue]linked to[/blue]
CallsLogged [black](Reference)[/black]
[red](One Loggers to One CallsLogged)[/red]

CallsLogged
[highlight](primary key = Reference)[/highlight]

Assistants
[highlight](primary key = AssignedReference)[/highlight]
[blue]Linked to[/blue] CallsLogged[black] (Reference)[/black]
[red](One Assistants to One CallsLogged)[/red]

[blue]Linked to[/blue]
Assistant[black] (AssistantRef) to [/black] [highlight]AssistantCode[/highlight]

Assistant
[highlight](primary key = AssistantCode)[/highlight]
[red](One Assistant to Many Assistants)[/red]

[/green]


I have amended my form as such.

I now get an error message.

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

The [green]Assistants[/green] table has the following fields.

AssistantRef
[highlight]AssignedReference(Primary Key)[/highlight]


I want the Assistants table to store all the instances of AssistantRef for each case logged(CallsLogged), so one assistant for one case.

------------------------
The [green]Loggers[/green] table has the following fields.

RegistrarRef
[highlight]AssignedReference(Primary Key)[/highlight]


I also want the Loggers table to store all the instances of RegistrarRef for each case logged(CallsLogged), so one Registrar for one case.


I want the form based on AssistantRef or RegistarRef, to be able to fill in the fields from the tables Assistant and Logger respectively.

I am exceedingly grateful for your help
I have tried quite a few different scenarios, and the only one that works is Assistant to CallLogger to Logger.

I really don't want the user every time they have to complete the form to fill out the fields, it just seems like a waste of time, if the data is already there, non duplicated, other that the actual registrar and assistant references, all the other data is linked.

Thanks again for all your help
PS I have attached a link to a screenshot of the relationship diagram to this.




Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

Hmm..I seem to have fixed my error by deleting a rouge record.

I have now created a dropdown box on AssistantRef and RegistrarRef.

I sourced the data from the table Assistant and Logger
Control source being AssistantRef and RegistrarRef respectively.

Now my next task is if the Assistant or logger is not available in the list I want a create new to appear in the list and execute an action of opening a form to enter a new call logger or Assistant.

Is this possible?

Thanks again for all the great help

Thank you,

Kind regards

Triacona

 

[Triacona]

I have put a value of new into the assistants table and this is working will give further news later...

Private Sub cmbAssistantCode_AfterUpdate()

    Dim stDocName As String
        stDocName = "NewAssistant"
    Dim stLinkCriteria As String

    If cmbAssistantCode = "New" Then
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.GoToRecord , , acNewRec
    End If
    

End Sub

Thank you,

Kind regards

Triacona