Linking Combobox back to table

[jlclark4]

Good Morning,



I have a form that I am trying to link to a table. My form has a autofill section where a person inputs there name and two other textboxes autofill with corsponding information. I am having trouble getting that information to link back to the table. I cannot put the "team_lead" into the control source to bound it to the table because it has "=Combo5.column(1)" in it to complete the autofill. What do I need to do to get these two autofill boxes in the table?

J

[jzwp11]

If the information is being autofilled, it implies that the information exists in another table. If that is the case, repeating the information again in another table violates normalization rules. It sounds like you are using a combo box to do the autofill, if that is the case you would only need to store the key field's value of the row source of the combo box in the underlying table.

In order to help more effectively, could you provide more detail about the table (field names) to which you are trying to bind the form and the table or record source that is supplying the info to the combo box?

[jlclark4]

If the information is being autofilled, it implies that the information exists in another table. If that is the case, repeating the information again in another table violates normalization rules. It sounds like you are using a combo box to do the autofill, if that is the case you would only need to store the key field's value of the row source of the combo box in the underlying table.

In order to help more effectively, could you provide more detail about the table (field names) to which you are trying to bind the form and the table or record source that is supplying the info to the combo box?

Its a very big table. See attached JPG
qryauto_fill
report_name
team_lead
manager

I am also having issues where it is placing numbers instead of the actual name into the Input Name field of the table. (The one that has a actual name was hand typed into the table)

AND I have the "Detail" next to Issue Type set to the control source of Detail on the table but it also doesn't transfer over.

[jzwp11]

From where is combo5 getting it's data (i.e. what is its row source)?

I am also having issues where it is placing numbers instead of the actual name into the Input Name field of the table. (The one that has a actual name was hand typed into the table)

If the input combo box's bound field is a number, then that is what will be put in the table which is the more correct way of doing it rather than putting the text value. With that said, the input value field in the table should be a long number datatype.

I think you have some problems with your table. If multiple people are associated with an issue, then that describes a one(issue)-to-many (people) relationship which is handled with a separate but related table. Additionally, if a person can be associated with many issues, then you actually have another one-to-many relationship. When you have two one-to-many relationships between the same 2 tables, you need a junction table to relate them.

A table to hold the basic issue info

tblIssues
-pkIssueID primary key, autonumber
-IssueNumber

A table to hold information about the people

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

The junction table would be as follows:

tblIssuePeople
-pkIssuePeopleID primary key, autonumber
-fkIssueID foreign key to tblIssues
-fkPeopleID foreign key to tblPeople

[jlclark4]

If the input combo box's bound field is a number, then that is what will be put in the table which is the more correct way of doing it rather than putting the text value. With that said, the input value field in the table should be a long number datatype.

The data for Combo5 is coming from qryAuto-fill which has no numbers in it. It is using the "entry" number. Which the only issue with that is when it comes to reporting, it wont show names just numbers...

[jzwp11]

When it comes to reporting, you would use a query that gets the name that is represented by the number. You would then base the report on the query.

Can you provide the SQL text of the Autofill query?


I still have some concerns about your table structure.

[jlclark4]

This is the only information I can get from the query, which means I may have done something wrong. I followed another tutorial online...

[jzwp11]

You are showing a design grid view; to get to the SQL view there should be an icon in the upper left corner that allows you to change views. Based on what you posted, it looks like the autofill query is pulling info from the Team Members table. Can you provide a list of the fields in the Team Members table? Or better yet, can you post your entire database? If you have sensitive data in the database. You can make a new database and then import the tables (structure only) along with the forms. With that we may be better able to help you reach a solution.

[jlclark4]

SELECT [Team Members].report_name, [Team Members].team_lead, [Team Members].manager
FROM [Team Members];


There are only 3 fields in the TeamMember Table: report_name, team_lead, and manager.

But if in the reports I can bring in the name form the number it gives then there are no issues...

[jzwp11]

I would recommend adding an autonumber primary key field to the table and then add it to your combo box. Put a long number datatype field in your main table where you can store that value from the combo box. In a query, you can then bring the info together.

I still have to question the structure of the Team Members table, if you have 2 people associated with a report (team lead and manager) that implies to 2 people (I assume), then the appropriate table structure is as follows:

tblReports
-pkReportID primary key, autonumber
-txtReportName

tblReportTeamMembers
-pkReportTeamMemberID primary key, autonumber
-fkReportID foreign key to tblReports
-fkPeopleID foreign key to tblPeople

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

[jlclark4]

Let me clarify, sorry. Report_Name is the main person inputing the data. That person has a team lead and manager assigned to them. Does that make since? It is not a actual report name.

[jzwp11]

That person has a team lead and manager assigned to them. Does that make since? It is not a actual report name.

Your statement further indicates that your table structure is not proper and needs to be corrected.

If a person is related to other people, then you need to associate the people in a table. With that you only need to reference 1 person in your issues table and you can always get people related to that person with a query.

tblPeopleRelation
-pkPeopleRelationID primary key, autonumber
-fkPPeopleID foreign key to tblPeople
-fkSPeopleID foreign key to tblPeople
-fkRelationshipID foreign key to tblRelationships (this captures how the person represented by fkSPeopleID is related to the person represented by fkPPeopleID whether they are a manager or team leader etc.)


tblRelationships (this table would hold records for team leader, manager, etc.)
-pkRelationshipID primary key, autonumber
-txtRelationship

Let me present an example.

Let's say we have 3 people in tblPeople

pkPeopleID|txtFName|txtLName
1|Ann|Doe
2|John|Public
3|Bill|Deer

Now let's say that we have 2 records in tblRelationships as follows
tblRelationships
pkRelationshipID|txtRelationship
1|team leader
2|manager

Now, let's say that John is Ann's team leader and Bill is Ann's manager. We would represent those relationships in tblPeopleRelation as follows

pkPeopleRelationID|fkPPeopleID|fkSPeopleID|fkRelat ionID
1|1|2|1
2|1|3|2

Record 1 (pkPeopleRelationID=1) shows that Ann (fkPPeopleID=1) has John (fkSPeopleID=2) related to her and John has the team leader relation (fkRelationID=1)

Record 2 (pkPeopleRelationID=2) shows that Ann (fkPPeopleID=1) has Bill (fkSPeopleID=3) related to her and Bill is her manager (fkRelationID=2)


In your main issues table, you would just reference Ann. You can always get her team leader an manager via a query with tblPeopleRelation

[jlclark4]

Okay, I see what your saying. Thank you for your help! I've learned a lot creating this thing and using this forum. It has been a life saver.

I only have one more issue and I think you'd be able to explain it better then what I've been reading.

I understand one can not lock individual fields without locking the entire record, correct? But isn't there a code that I can put into the form that locks fields with data in them and allows those that are null (or empty) to still be editted when the entry is re-opened?

[jzwp11]

You can lock/unlock controls on the form with code depending on whether or not there is a value in the control (that is tied to the underlying table). The code would go in the On Current event of the form.

The code would look something like this (you'll have to substitute your own control names). You will have to repeat the code for each form control you want to lock/unlock. The Me. notation is just a shorthand for the form name on which the control is located.

If IsNull(Me.yourcontrolname) Then
    Me.yourcontrolname.Locked = False
Else
    Me.yourcontrolname.Locked = True
End If

[jlclark4]

Private Sub Form_Current()
If IsNull(Me.txtCErt) Then
    Me.txtCErt.Locked = False
Else
    Me.txtCErt.Locked = True
End If
End Sub

How do I use this code mulitple times? I tried just adding another Private Sub Form_Current which did not work. Also tried repeating the If IsNull after the End If and it did not work.

For example, I want to also lock "txtDate"