Update Table Without Primary Key

[Guest]

I have a table that does not have a primary key. This table contains records
of requests to change employee computer access. I am using a form to update
the records in the the table. Each request has a request number, but there
may be more than one entry for a particular request number because one
request might cover two or more employees.

The problem I'm running into is, when I select a record on the form that has
the same request number as another record, and enter a project close date,
the correct record isn't getting updated. What do I need to do to ensure the
correct record is updated?

Thanks!
GwenH

 

[Jeff Boyce]

Gwen

I believe your description contains your solution.

Set up a primary key (unique identifier) on the table. That will allow you
to choose the "right" record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I tried that, and it didn't work. I am using a listbox to navigate amongst
the records on my form. When I click the record I want to update in the list
box, all the information related to that record is displayed in the other
fields of the form. But when I update one field on the form and click the
save button on the form, the date gets added to the wrong record.

This happens regardless whether the list box has two columns (primary key
and request number) or one column (request number only).

Any ideas?

Thanks!

 

[Jeff Boyce]

Gwen

The listbox and the form may not have the same source. How do you "get" the
record to display in the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

The form is based on a query that pulls data from the table. The only thing
the query does is filter out requests that already have a closed date.

 

[Jeff Boyce]

Gwen

You seemed to draw a connection between your listbox and the record in the
form. How are you using the listbox selection to "get" the correct record
to display in the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Gwen

You seemed to draw a connection between your listbox and the record in the
form. How are you using the listbox selection to "get" the correct record
to display in the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Sorry about the blank message ... I am too trigger happy on the Post button
today. Anyway, I used the listbox wizard to setup the listbox; its Row Source
Type is set to Table/Query, and its Row Source is set to "SELECT
openProjects.requestNumber FROM openProjects," with openProjects being the
query I mentioned earlier.

 

[Guest]

Found the solution! I changed the Row Source to this, and it works perfectly:

SELECT Requests.requestNumber FROM Requests WHERE (((Requests.closeDate) Is
Null));

In other words, I am now pulling data straight from the table instead of a
query.

However, after I select a record with the list box and add a close date for
the request, then click a Save button, I am using VBA to requery the list box
(so it only shows open requests), and then move to the first record in the
list box. It's doing the requery, but the list box doesn't move to the first
record. Here's my code for the Save button:

'Button to save the changes to the current record
Private Sub saveChanges_Click()
On Error GoTo Err_saveChanges_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Changes to This Record Have Been Saved", vbOKOnly, "Changes Saved"
Me.Form.Requery
Me.List0.Requery
Me.List0.SetFocus
DoCmd.GoToRecord , , acFirst

Exit_saveChanges_Click:
Exit Sub

Err_saveChanges_Click:
MsgBox Err.description
Resume Exit_saveChanges_Click

End Sub

Any idea what I'm doing wrong?

Many thanks!

 

[Jeff Boyce]

Gwen

I'm still a bit confused about the connection between your form and your
listbox.

Based on what I've understood, I'd probably approach this a different way.
See if this description offers any hints to what you're trying to do...

I have a table of information.

I create a query that lists several fields from that table, based on one of
the fields (?your "CloseDate is Null"?)

I create a form, unbound.

I add a combo box in the form's Header, based on the query. This gives me a
way to select a specific record I'd like to modify.

I add a bit of code to the combo box's AfterUpdate event -- something like:
Me.Requery

I create a query that returns the fields from the table at the top, but add
a selection criterion something like:

Forms!MyNewForm!MyComboBox

This restricts what the form displays to the record selected in the combo
box.

I modify the record source for the form to be this second query.

Now, when I open the form, the combo box is empty, so the form's underlying
query returns no record. If I select a record in the combo box, the form
requeries and displays the fields of the selected record.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP