Select multiple records and loop

[Gasman]

I think you are making it much harder than need be.?
I have never used a multiple combo box as data entry, only one column, as in the example I have given.
That search form that I copied from allen browne allows you to search what you want on anything you want.

So if you got you mind off 'must have' a combo box and thought of a text box and list box, you would get what you want so much easier. ?

I cannot help on anything I have not done myself, or seen myself. Sorry.
If you notice my signature I new to Access also.

 

[jaryszek]

Thank you Gasman,

I have found the solution.

I did only one combobox and add vba code on form open:

Private Sub Form_Load()

Dim strSQL As String

strSQL = "SELECT [Nazwisko i imię pełnomocnika], [Numer pesel pełnomocnik] FROM tb_main;"

With Com_columns
    .RowSource = strSQL
End With

End Sub

now I have combobox with 2 fields: one with names (Nazwisko i imię pełnomocnika) and second with (Numer pesel pełnomocnik).

user can type name (string) and see name and unique id attached to it.

But where user is typing number the code is running:

Private Sub Com_columns_Change()

Dim strSQL As String

If Len(Com_columns.Text) > 1 Then
   Exit Sub
End If

If IsNumeric(Left(Com_columns.Text, 1)) = True Then
    strSQL = "SELECT [Numer pesel pełnomocnik], [Nazwisko i imię pełnomocnika] FROM tb_main;"
    Com_columns.RowSource = strSQL
    Exit Sub
Else
    strSQL = "SELECT [Nazwisko i imię pełnomocnika], [Numer pesel pełnomocnik] FROM tb_main;"
    With Com_columns
        .RowSource = strSQL
    End With
End If

End Sub

this code is checking if first letter is number (for unique ID) or string.

If number combobox_rowsource is changed and numbers are in first columns within combobox.

One more problem is when will be more then one name.
Maybe i should also write the code to warn user about checking names where they are duplicated?

Jacek

 

[Gasman]

Isn't that going to be a little hard to determine when using numbers.

181216 could be Jerry Wzinski
192186 could also be another Jerry Wzinski

but you are never going to see them together, due to the space between the numbers?

One more problem is when will be more then one name.
Maybe i should also write the code to warn user about checking names where they are duplicated?

Jacek

 

[The_Doc_Man]

It is of course your database and your choice, but just a word of friendly advice. You can save yourself some heartache by not using names with embedded spaces. If you run the words together, you don't need brackets for those fields. Less typing and still not a big deal to understand what is being referenced.

Now, if it EVER happens that you need to upgrade a backend from Access to SQL Server or any other active ODBC/SQL engine, you MUST get rid of the spaces. But that is a decision to be made only when necessary.

 

[jaryszek]

Thank you Guys!

Yes spaces are bad, I know this But like always in my company - this must be with spaces in order to pleased the users

but you are never going to see them together, due to the space between the numbers?

Good point. So i wrote the code if there are duplicates and if yes - show msgbox and warn user. This situation fortunately will be not often so this will be satysfying for my project.

Thank you one again.
Jacek

 

[Minty]

Thank you Guys!

Yes spaces are bad, I know this But like always in my company - this must be with spaces in order to pleased the users

You can show your users what they want - but don't use the same in your database. Just because a field is called BestPracticeNoSpaces doesn't mean that is what you display.

 

[jaryszek]

Minty, good point.

Where can you replace technical name of column for "user name" of column?

Jacek

 

[Minty]

Are these access tables or linked from another type of database?

Look at the caption property on your form designs

 

[jaryszek]

these is split database - all in access.

 

[Minty]

In that case look at field caption property in the table design, this will appear as the field label automatically, and is used as the caption on datasheet views.

 

[jaryszek]

wow it is nice thank you Minty !

Jacek

 

[Gasman]

You are aware that you can call the fields what you like, and have more meaningful captions for them though?


Edit: oops, can now see I was a little late posting this. Ah well.

Thank you Guys!

Yes spaces are bad, I know this But like always in my company - this must be with spaces in order to pleased the users



Good point. So i wrote the code if there are duplicates and if yes - show msgbox and warn user. This situation fortunately will be not often so this will be satysfying for my project.

Thank you one again.
Jacek

 

[The_Doc_Man]

My question comes about with just a clarification. You DO hide the inner workings from the users by means of a switchboard or dispatcher form, don't you? End users in a shared database app should NEVER EVER IN A GAZILLION YEARS directly view the basic objects of a database via the Navigation Pane. EVER.

If you shield the users from the madness of seeing the inner workings, then what you label ANYTHING is up to you. At worst, it takes a little extra work to find where the correct place is to assert your desired moniker for each field in each place it is shown.

 

[jaryszek]

Thank You The Doc Man for your help,

I will consider all of yours tips and solutions.
And of course users can not have access to inside of database. I know... ;-)

Jacek