Solved Problem with list box selecting individual records (1 Viewer)

DinkyDon

New member
Local time
Today, 13:38
Joined
Jun 9, 2021
Messages
8
Hi, I'm currently building a database with a 'master' form that I want everyone to use instead of meddling around in the tables and such. I've got a couple of sub-forms opening up from here, including new record and edit record.

The problem lies with edit record, as I have made a list box to display the results from a search box. The results show up fine, and all records from each client are visible. However, when I go to select a specific record, it defaults to only one record per client. As most clients have multiple instances of software/hardware, it doesn't allow me to open any of the alternative records, only one record per client.

I'm using a button to open the edit data sub form, and the code I have for the on click event is:

DoCmd.OpenForm "EditData", , , "[Client_Database.Client]=" & " ' " & Me.EditRecordResults.Column(0) & " ' "

The EditRecordResults is the list box, and the after update code I have for the list box is:

EditRecordButton.Enabled = True

EditRecordButton is the same button I have the on click event for.

Any ideas what could be causing this? I'm not sure if the event code causing the issue, or if access's default list box settings are limiting my options, although I don't know how to change these either.

Any help would be greatly appreciated!
 

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
is your listbox designed so you can select multiple items from the list?
you need to browse through each .ItemsSelected of the listbox:


Diff:
Dim sClient As String
Dim var As Variant
For Each var In Me.EditRecordResults.ItemsSelected
    sClient = sClient & Me.EditRecordResults.ItemData(var) & ","
Next
If Len(sClient) <> 0 Then
    sClient = Left$(sClient, Len(sClient)-1)
End if
DoCmd.OpenForm "EditData", , , "[Client_Database.Client] In (" & sClient & ")"
 

DinkyDon

New member
Local time
Today, 13:38
Joined
Jun 9, 2021
Messages
8
is your listbox designed so you can select multiple items from the list?
you need to browse through each .ItemsSelected of the listbox:


Diff:
Dim sClient As String
Dim var As Variant
For Each var In Me.EditRecordResults.ItemsSelected
    sClient = sClient & Me.EditRecordResults.ItemData(var) & ","
Next
If Len(sClient) <> 0 Then
    sClient = Left$(sClient, Len(sClient)-1)
End if
DoCmd.OpenForm "EditData", , , "[Client_Database.Client] In (" & sClient & ")"
I've tried this code, but I keep getting one of two errors.

Either error code 3075 for having a missing operator in query expression:

[Client_Database.Client] In (

Or error code 3085 for having an undefined function in expression, however the undefined function is the name of the software the client has.

Any idea how to fix this?
 

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
change it to:
Code:
Dim sClient As String
Dim var As Variant
For Each var In Me.EditRecordResults.ItemsSelected
    sClient = sClient & "'" & Me.EditRecordResults.ItemData(var) & "',"
Next
If Len(sClient) <> 0 Then
    sClient = Left$(sClient, Len(sClient)-1)
End if
DoCmd.OpenForm "EditData", , , "[Client_Database.Client] In (" & sClient & ")"
 

moke123

AWF VIP
Local time
Today, 08:38
Joined
Jan 11, 2013
Messages
2,471
Is your listbox multi-select?
How many records are you trying to edit at once?
whats the sql of your listbox?
whats the editdata form look like?
 

DinkyDon

New member
Local time
Today, 13:38
Joined
Jun 9, 2021
Messages
8
change it to:
Code:
Dim sClient As String
Dim var As Variant
For Each var In Me.EditRecordResults.ItemsSelected
    sClient = sClient & "'" & Me.EditRecordResults.ItemData(var) & "',"
Next
If Len(sClient) <> 0 Then
    sClient = Left$(sClient, Len(sClient)-1)
End if
DoCmd.OpenForm "EditData", , , "[Client_Database.Client] In (" & sClient & ")"

This code made it so that when I clicked on the edit record button, the record itself was blank. Presumably this is a new record, as the records I have selected in testing this still exist.

Is your listbox multi-select?
How many records are you trying to edit at once?
whats the sql of your listbox?
whats the editdata form look like?

My listbox isn't multi select, should it be?

I'm only trying to open and edit one record at a time. Each client has multiple records however, but I just want to open each one, to change maintenance/service intervals and such.

The only code I have for my listbox is:

Private Sub HWmaintenanceDueResults_AfterUpdate()
DueServiceHWReport.Enabled = True
End Sub

The edit data form is just a simple form with one column of labels on the left. and then a column of text boxes/date boxes for the relevant data.
 

moke123

AWF VIP
Local time
Today, 08:38
Joined
Jan 11, 2013
Messages
2,471
I'm using a button to open the edit data sub form, and the code I have for the on click event is:

DoCmd.OpenForm "EditData", , , "[Client_Database.Client]=" & " ' " & Me.EditRecordResults.Column(0) & " ' "

so what is the rowsource of your listbox?
What is in column(0)? Is it text like a name? or is it a number as in a ClientID field (primary key)?

if its text it needs delimiters, a number does not.

text - DoCmd.OpenForm "EditData", , , "[Client_Database.Client]=""" & Me.EditRecordResults &""""

number - DoCmd.OpenForm "EditData", , , "[Client_Database.Client]=" & Me.EditRecordResults

also is column(0) the bound column?
 

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
I'm only trying to open and edit one record at a time.
your previous code is doing that already.
just upload your db, so we can resolve this the soonest.
we cannot visualize what you have.
 

DinkyDon

New member
Local time
Today, 13:38
Joined
Jun 9, 2021
Messages
8
so what is the rowsource of your listbox?
What is in column(0)? Is it text like a name? or is it a number as in a ClientID field (primary key)?

if its text it needs delimiters, a number does not.

text - DoCmd.OpenForm "EditData", , , "[Client_Database.Client]=""" & Me.EditRecordResults &""""

number - DoCmd.OpenForm "EditData", , , "[Client_Database.Client]=" & Me.EditRecordResults

also is column(0) the bound column?

The row source of my listbox is as follows:

SELECT [SearchQuery].[Client], [SearchQuery].[Contact], [SearchQuery].[Hardware], [SearchQuery].[Software] FROM SearchQuery ORDER BY [Client], [Contact], [Hardware], [Software];

Essentially this takes from my SearchQuery (imaginatively titled) for each of the four categories in the list box.

Column(0) houses text, for the clients name.

In bound column, I have selected 4. I believed that this was to select all columns, or am I wrong and this has selected column 4 to be bound?

your previous code is doing that already.
just upload your db, so we can resolve this the soonest.
we cannot visualize what you have.

I will make up some dud values in the database but keep the format the same, and I'll try and upload this
 

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
In bound column, I have selected 4.
column 4 is [Software], you should probably(?) use column 1 (client) as bound column.
since on pos#1, that is your criteria.

what criteria do you want to open the form?
specific software, hardware for all clients?
 

DinkyDon

New member
Local time
Today, 13:38
Joined
Jun 9, 2021
Messages
8
column 4 is [Software], you should probably(?) use column 1 (client) as bound column.
since on pos#1, that is your criteria.

what criteria do you want to open the form?
specific software, hardware for all clients?

Yes, each entry has a unique serial number in hardware/software alongside the product that the client owns. If this could be made the criteria that would be brilliant, but not every record has both hardware and software.
 

Attachments

  • DatabaseAsIsDudValues.accdb
    4.1 MB · Views: 46

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
is this what you need
 

Attachments

  • DatabaseAsIsDudValues.accdb
    4.1 MB · Views: 52

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
This hasn't worked, it still has the same problem of opening one record per client.
of course it will open 1 record per client, becoz that is the way you build your form.
change your form to datasheet or continuous or split form so it will show multiple records.
 

DinkyDon

New member
Local time
Today, 13:38
Joined
Jun 9, 2021
Messages
8
of course it will open 1 record per client, becoz that is the way you build your form.
change your form to datasheet or continuous or split form so it will show multiple records.

I'm not trying to open multiple records at once, the problem is that if a client has 5 records (one for each piece of hardware/software) the editdata form will default to only of these records per client. So if it defaults to record 2, but I'm trying to select record 3, 4, or 5 to edit, it will still only open record 2, regardless of which record I click in the list box results.
 

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
so EditData will only show record that you Selected on the listbox.
I added ID (autonumber) to your table.
check again.
 

Attachments

  • DatabaseAsIsDudValues.accdb
    4.1 MB · Views: 18

DinkyDon

New member
Local time
Today, 13:38
Joined
Jun 9, 2021
Messages
8
so EditData will only show record that you Selected on the listbox.
I added ID (autonumber) to your table.
check again.

Amazing that has sorted it, thank you so much! I've been banging my head against a wall for a while about this one!
 

arnelgp

once i caught a fish alive...
Local time
Today, 20:38
Joined
May 7, 2009
Messages
13,236
i added autonumber (id) to your table.
in future, it won't hurt if you add same to other table.
this (id, autonumber) is your unique identifier for each record.
 

moke123

AWF VIP
Local time
Today, 08:38
Joined
Jan 11, 2013
Messages
2,471
You should re-think your table structure. It is wrong. Looks more like a spreadsheet than a relational database.
You should probably have a tables like clients, contacts, hardware, software, service dates, etc.
 

Users who are viewing this thread

Top Bottom