Access Row source not working

Question

Hi. I have an Access form. This form has one field called CR Raiser, which uses a table called Contacts to get a persons name, and uses the following code in the row source. The table is populated using another form to enter personal details, name, number etc. It also asks for workgroup.

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] AS Expr1 FROM Contacts ORDER BY Contacts.[Last Name];

The second field is called Assignee Group. I want this field to use the same contacts table, but instead of using the above code, i just want it to list the entered workgroups. Is there a code i need to enter in the "Row Source" field similar to the above code?

Answer 1

I could follow this until the update query part then i just got completely confused. Can you please explain again. The workgroup name field is needed in the issues log.

The impacted fields are as follows

1. On the issues form the field is called CR Workgroup.
2. On the issues table there is NOT a field called CR Workgroup
3. On the contacts table the field is called SM7 Workgroup
4. On the contacts form the field is called SM7 Workgroup
5. I have created a table called Workgroups which has two fields - WworkgroupID which is an autonumber, and Workgroup which will be the list of workgroup names.

Answer 2

Let's first deal with the problem of the values entered into the Task Number, TT Impacted and Time controls in the form not being inserted into the columns in the Issues table.  The form should have as its RecordSource property either the name of the Issues table or a query which returns al the columns from the table.  Using a query is generally better as it allows you to order the records on one or more columns so that they appear in a logical order in the form.

Within the form you should have controls, text boxes in this case, whose ControlSource property is in each case the name of the relevant column in the table, i.e. Task Number, TT Impacted or Time.  Note however that naming a column Time is not a good idea as Time is the name of a built in function, so as a 'reserved' word should be avoided as an object name.  Something more specifically descriptive like CR_Time or similar would be better.

You should then have no difficulty entering data into the controls and it being assigned to the relevant column in the current row in the table when the form is close, you move to another record or otherwise explicitly save the record.

As regards the CR Workgroup column in the Issues table, the first question to be addressed is whether that column is either necessary or desirable.  As I said in my first reply this depends on whether it is functionally determined by the contact or not.  What this means is that when you select a contact as the CR Raiser should the value of CR Workgroup in the Issues table always be the value of Workgroup in the row in the Contacts table for the selected contact?  Or is it legitimate for the value of CR Workgroup in the Issues table to differ from that value?  If the former then you don't need the column in Issues and should return the value from Contacts in a computed control as I described in my earlier post, changing the column names in the combo box's RowSource property to the actual names.  BTW I should have mentioned that the CR Raiser's combo box's ColumnCount property should be changed to 3 in this scenario.

If on the other hand the value can legitimately differ from the value in Contacts you will need to address a fundamental design issue in your database.  You will firstly need to introduce a Workgroups table if you do not currently have one.  This will be a table with one row for each possible workgroup, so would be set up like this:

WorkGroups

....WorkgroupID (PK)

....Workgroup

The primary key WorkgroupID column can for convenience be an autonumber.

Before doing anything further back up the database.

You then need to fill this table with the names of all workgroups currently listed in Contacts, using the following append query:

INSERT INTO Workgroups(Workgroup)

SELECT DISTINCT Workgroup

FROM Contacts

WHERE Workgroup  IS NOT NULL;

You next need to add a WorkGroupID column, of long integer data type, to Contacts and insert values into it with the following update query:

UPDATE Contacts INNER JOIN Workgroups

ON Contacts.Workgroup = Workgroups.Workgroup

SET Contacts.WorkgroupID = Workgroups.WorkgroupID;

Once you are happy that the new WorkgroupID column in Contacts has the correct values you can delete the redundant Workgroup column from Contacts.  Then create a relationship beteen Contacts and Workgroups and enforce referential integrity.

Whether or not you need a Workgroup column in Contacts as discussed above, you should do the above in any case; it's just a matter of good database design.

If on the basis of the discussion above you conclude that a CR Workgroup column is legitimately required in the Issues table, this should be of a long integer data type and its bound control in the issues form should be a combo box set up as follows:

RowSource:     SELECT WorkgroupID, Workgroup FROM Workgroups ORDER BY Workgroup;

BoundColumn:   1

ColumnCount:   2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the dimension is zero to hide the first column.

For selecting a workgroup in a contacts form you'd use a combo box set up in the same way.

To enter a workgroup not currently represented in the Workgroups table you can out the following code in the NotInList event procedures of the workgroup combo boxes in both the issues and contacts forms:

    Dim ctrl As Control

    Dim strSQL As String, strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    strSQL = "INSERT INTO Workgroups(Workgroup) VALUES(""" & _

            NewData & """)"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        CurrentDB.Execute strSQL, dbFailOnError

        Response = acDataErrAdded

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

When the user types a new workgroup name into the combo box and confirms it at the prompt, a new row will be inserted into the Workgroups table and the combo box's list updated to show the new workgroup.

Answer 3

I feel i should already have mentioned this, but my Access knowledge is very very basic. I did it many years ago but have now forgotten most of it, so what you have put above i do not understand. Sorry.

I will try to explain the issue in more detail.

I have a table called Contacts. Within this table there are multiple fields. These include First Name, Last Name and workgroup, amongst others. To enter information into this table i have a form.

I then have a seperate table called "Issues". Again i have set up a form to complete the data entry for this. The fields required are as follows

1. CR Number
2. CR Raiser - this is based on the names entered into the Contacts table and uses the code mentioned above
3. CR Workgroup - this i cannot get to work. I want it to use the Contacts table to show a list of possible entries, or allow the user to over type and enter anything
4. Change Date
5. Task Number - *
6. Status
7. Issue
8. TT Impacted - *
9. Time - *
10. Comments

Now the 3 entries with * - when i enter the data into these fields, it does not copy across to the table "Issues".

Hopefully this helps a bit. Happy to discuss further or to try and send a copy of the form to show you

Answer 4

If the Assignee Group is functionally determined by CR Raiser, then you should not have a column for it in the table to which your form is bound.  That introduces redundancy; the table is not normalized to Third Normal Form (3NF) and is consequently open to the risk of update anomalies.  Instead you should include an unbound text box control in the form to show the value from the Contacts table.

Firstly amend the RowSource property of the CR Raiser combo box to:

SELECT ID, [Assignee Group],[First Name] & " " & [Last Name] FROM Contacts ORDER BY [Last Name];

and amend its ColumnWidths property to 0cm;0cm;8cm (Access will automatically convert to inches if you are not using metric units).

Then set the ControlSource property of the unbound text box to:

=[CR Raiser].Column(1)

where CR Raiser is the name of the bound combo box.  The Column property is zero-based, so Column(1) is the second column, Assignee Group.

If the Assignee Group is not functionally determined by CR Raiser, i.e. its value can legitimately be amended form that in the form's table from that for the selected contact in the Contacts table, then set up the CR Raiser combo box in exactly this way, but this time include a text box bound to an Assignee Group column in the form's table, and in the AfterUpdate event procedure of the CR Raiser combo box assign a value to the Assignee Group control with:

[Assignee Group] = [CR Raiser].Column(1)

Answer 5

Sorry, but without knowing more about your data model, it's pretty difficult to say. In what table is workgroup stored?