I need a solution similar to the Microsoft Customize Quick Access Toolbar dialog

[Hinson3]

Hello all,

I need a solution similar to the Microsoft Customize Quick Access Toolbar dialog. Where you select the item from one side | Click Add and the selected item is copied to the selected item list/box. You repeat the process until all items that apply are selected. Then Click OK and the selected items (PrimaryID) are copied/added to a table/query.

[PhilOfWalton]

Your question is too vague to answer. Wha are these "Items"? Where do they come from? Are they values from a table?

I have attached what I think you mat be looking for where there is a table of boat types and the selected ones are in the right hand listbox and the ones, not selected in the left hand box.

They are moved between the boxes by either using the arrows or double clicking the type of boat

Phil

[Hinson3]

So sorry, it made so much sense in my mind and strangely YOU NAILED IT! with Equipment Avail - Equipment Selected example.
That is exactly what I want! Here are more details. . .

I have a database that tracks the each members visit and requested service per day.

Tables and major field names
MemberService – MSID-PK; Account#, MemberFName,
MemberLName, SignInDate, SignInTime, EmpID-FK, PSID-FK
Employee – EmpID-PK; EmpFName, EmpLName, Depart . . .
ProdServ – PSID- PK; ProductName, ServiceName, Description
PK = Primary Key and FK = Foreign Key

Step #1: A member walks in and SignIn on a dataEntry form - that data goes straight to the MemberService Table.
Step #2: An employee provides a service(s) to that member. The member only wants one service i.e. Reset password
Step #3: The employee scrolls through a list of 75 and selects "Reset Password" from the ProdServ table
Click Submit - Done! - The Primary Key of the selected service is populated in the MemberService table. I will generate different queries from that later.

However, if the member wants multiple services during the current session . . .
I would like the employee to be able to select all that applies and actually see the services selected.(like your example) Or
Click OK and the selected services are displayed somewhere on the form. Point, the employee will need to see what they selected for the WrapUp.

The PSID key(s) is populated in the MemberService table in the PSID-FK field i.e.(34, 44, 75)
However when I run my query it will display the services like a List Box SName.value
PSID 34| ServiceName | Reset password
PSID 44| ServiceName | Update name, address and email
PSID 75| ServiceName | Copies, Fax

I tried using MS Access Listbox - but ran into the pessimistic locking thing. That's a problem because the database will be shared. Could not get Optimistic locking to work either. So, I'm reaching out to the community for HElp!

If it's not possible to add all selected services into one field - and then parse each service for a query or report ---
they can have their own record (MSID-PK). We would need to copy the MSID, account# and memberName to each new record to show it was one visit.

I have just learned the importance of normalization and I am trying to get warm and fuzzy with many to many relationship; the results of this database will tell. I hope this provides a better picture of what I'm trying to achieve.

End result: Query showing the Count of services grouped by Date and Employee#

Member Name (First&Last Concatenated); Account Number; Service, SignInDate, SignInTime, Emp#

This is my first post, thanks in advance for your help. Oh! I can provide pics and a sample of what I have . . . if that's okay.

[PhilOfWalton]

Great, well at least I understand the problem ... solving it is much more tricky in a multi-user situation.
Do you have some sort of table to show which users are logged on. Is this the same as your Employee table? We will need to know which user has made which selection(s) from the ProdServ table.
Unfortunately, the simple setup in the example I posted works for a single user by simply having a field called "Selected" (True/False) in the Equipment table (your ProdServ table) but with different users changing the value of "Selected" that won't work.

I suspect the answer will be to have a join table joining the User (Employee?) to the ProdServ table.

Phil

[Hinson3]

"Do you have some sort of table to show which users are logged on. Is this the same as your Employee table?"
No, but I'm on it! I think I saw an example awhile ago and bookmarked it. I will post pics when done.

Thank you Phil

[zmbd]

I have a functionally similar form to Phil's that I use to set/show instrument configurations. That works within a multi-user environment.

The list boxes are based on a couple of queries that are then used to populate the list boxes.

One table has all known equipment (this is not the inventory table which has the serial number and other inventory specific information - :) )

One table has the Instrument names

One table has the current relationship between the instruments and the equipment - profile

Query for the available equipment list box, is a non-matching parameter query. The parameter is from a combobox on the form that allows the user to select the instrument. The query compares the current "profile" table against the parts table, any non-matching items are added (listbox.add)to the list.

Both list boxes have two columns, bound column is 1, first column is width 0, second width is 2inch (same for both list boxes) so when the record is appended to the list box both the PK and the Human friendly text is added (both list boxes!) which makes things easier for updating later....

The current instrument profile is much simpler, it is also a parameter query using the afore mentioned combobox against the profile table. The items in this query are entered into the listbox.

>> Actually I use a query against the profile table that has the related equipment table so that I have the equipment name field available :) <<

Looking at Phil's image, I have a very similar arrangement for the controls... different icons; however, same effect.

There is also a reset that will requery the two list boxes for current configuration just in case another user happens to be altering the same profile. (I call the on_open event where the initial setup code for the list boxes is located)

The "Current Profile" list box is basically a temp table at this point.

There is a save button...
This is where the magic happens :0)

First I open a locked recordset against the profile table. Prior to this, to populate the Current profile list box I had a snapshot recordset. The two are compared, locked against snapshot, any differences and the user is informed that someone else has edited the instrument profile and is asked if they want to discard their changes and reset to the new profile or proceed with the changes. Otherwise if both match then I step thru the Current Profile list box...

Stepping thru the list box, I do a findfirst and on no-match I add records to the profile table AND I build a string of the Equipment primary keys... using that string, I then run
The following is pseudo code

DELETE [PK_InstrmntPrfls] 
FROM tbl_InstrmntPrfls
WHERE(
([fk_instrmnts]=((Value from cbo))
AND
([PK_InstrmntPrfls] Not in (string from listbox)

which removes the non-matching entries.

Against a few hundred records over a fairly fast and stable network this takes a few seconds for the entire update to occur.

Once done, I call the On_Open event to re-set the listboxes to current... shouldn't change; however, if another user saves right after the first user then the first user has the current instrument profile.

(Two refernences:
+ Recordset.LockEdits Property (DAO) -

I go one step further and all of this within a transaction environment (basic explanation: How to: Use Transactions in a DAO Recordset and from our own Adezii: DAO Transaction Processing - What is it? - a link to error checking here too :) ((ok, four references total) :)) I use the transactions because I have a series of related records that have to be saved and if any of the tables cannot be locked or the record fails to save for any reason I can roll back the whole thing)

I know I've left something out, that whole form took me weeks to do and I'm sure something slipped my mind.
Here's a historical link to the project https://bytes.com/topic/access/answe...-selection-box

[PhilOfWalton]

I an always concerned about record locking and "someone else has changed a record".
I think a better solution is for each user to have their own table. Actually it is everyone's table, but within it there is the user's name and computer name (in case they log on to another computer)

This, I'm afraid is a bit long and complicated, so please bear with me.

First we need to create a table to hold the all the available services, but personalised for that user.
I an afraid, from the point of view of testing, I have had to use existing tables, so you will have to change names as necessary.

 
Table5
    EquipmentTypeID     Long   NOT a key
    User                Text
    Computer            Text
    Selected            Y/N
 

Now we need routines to get the user name and the computer name

In the header of module you need

Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 

This gets the user name

Function Mnu_fOSUserName() As String
    ' Returns the network login name
 
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        Mnu_fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        Mnu_fOSUserName = vbNullString
    End If
 
End Function
 

and this the computer name

Function fOSMachineName() As String
    'Returns the computername
 
    Dim lngLen As Long, lngX As Long
    Dim strCompName As String
 
    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        fOSMachineName = Left$(strCompName, lngLen)
    Else
        fOSMachineName = ""
    End If
 
End Function
 

With me so far?

Now on the form where the 2 list boxes appear, we need to create that users personal list of services.
So the code below firstly deletes the list for that user on that computer, then re-creates it.

Private Sub Form_Open(Cancel As Integer)
 
    Dim ComputerName As String
    Dim UserName As String
    Dim MyDb As Database
    Dim SelectedSet As Recordset, ProdServSet As Recordset
    Dim SQLStg As String, SQLStg2 As String
 
    ComputerName = fOSMachineName
    UserName = mnu_fOSUserName
 
    SQLStg = "SELECT * FROM Table5 "                ' Table of selected services
    SQLStg = SQLStg & "WHERE User = " & Chr$(34) & UserName & Chr$(34)
    SQLStg = SQLStg & " AND Computer = " & Chr$(34) & ComputerName & Chr$(34) & ";"
 
    Set MyDb = CurrentDb
    Set SelectedSet = MyDb.OpenRecordset(SQLStg)
 
    With SelectedSet
        Do Until .EOF
            .DELETE
            .MoveNext
        Loop
        .Close
        Set SelectedSet = Nothing
    End With
 
    SQLStg2 = "SELECT * FROM EquipmentTypes;"       ' All the services
 
    Set ProdServSet = MyDb.OpenRecordset(SQLStg2)
    Set SelectedSet = MyDb.OpenRecordset(SQLStg)
 
    With ProdServSet
        Do Until .EOF
            SelectedSet.AddNew
            SelectedSet!EqipmentTypeID = !EquipmentTypeID
            SelectedSet!User = UserName
            SelectedSet!Computer = ComputerName
            SelectedSet!Selected = False
            SelectedSet.Update
            .MoveNext
        Loop
        .Close
        Set ProdServSet = Nothing
        SelectedSet.Close
        Set SelectedSet = Nothing
    End With
 
End Sub
 
 

I wont go any further till I know you have this working with your own data.

Phil

[zmbd]

One will run in to a locked record and this record was changed by an other user at some point even with optimistic record locking and a split database... Forms are mostly just a snapshot of the record source at the time of load and these things happen and have to be accounted for.

The "active user" table helps with tracking down who has the record locked; however, I'm not sure if it is of any help in this case. There are several thread covering an "idle timeout" and:
home > topics > microsoft access / vba > insights > inactivity detection in access

I have used a much simpler timer event that checks a table in the back-end for a release request. The more essential to have the user backed out of a table the more frequent the check.

We are now however drifting a tad off topic.

[Hinson3]

Okay, I apologize for the delay. Thank you Zmbd, I like both ideas as its very important that this database is able to handle and multiuser environment. I will try Phil's 1st.

I completed the following:
A table with Employee UserLogin, EmpPassword and Computer
A Logon form
A Module to get Computer and user name
fOSUserName and fOSMachineName function
Created a WrapUpForm and added the SQLStg2 = "SELECT * FROM ProdService; code

I must admit I'm kinda lost, but will keep trying.
Question: Do I need to create another table to hold the selected services or can I add them to the main Member table? I would like to tie the MSID and ProdServID together. So when they select multiple services for an interaction the MSID and ProdServID will keep them together.

The code "Set MyDb=CurrentCD
Set SelectedSet = MyDb.OpenRecordset)SQLStg) gets an error of Run-Time error 3078. I created two list boxes one Available Services and Selected Services - I did not create another table. Your thoughts?

Thank you both for your patience,

[PhilOfWalton]

Sounds as if you've made good progress.
Your error 3078 may be a typo error on your part.
The first line should be

Set MyDb = CurrentDb
 

and the second line has 2 right brackets.

Moving on, I forgot in Table5 we need a description (I have called it EquipmentType, but I guess your equivalent will be MemberService)

Now create an unbound form with 2 listboxes on it. I call them ListNotSelected & ListSelected.

I need to go back to the bit where you created the part of the table for your user on that machine. It needs a couple of alterations.

I will just give the end bit (changes are in bold type)

SQLStg2 = "SELECT * FROM EquipmentTypes;"       ' All the services
 
    Set ProdServSet = MyDb.OpenRecordset(SQLStg2)
    Set SelectedSet = MyDb.OpenRecordset(SQLStg)
 
    With ProdServSet
        Do Until .EOF
            SelectedSet.AddNew
            SelectedSet!EquipmentTypeID = !EquipmentTypeID
            SelectedSet!EquipmentType = !EquipmentType
            SelectedSet!User = UserName
            SelectedSet!Computer = ComputerName
            SelectedSet!Selected = False
            SelectedSet.Update
            .MoveNext
        Loop
        .Close
        Set ProdServSet = Nothing
        SelectedSet.Close
        Set SelectedSet = Nothing
    End With
 
    ListNotSelected.Requery
 
End Sub
 

Back to the list boxes.
The row source of the ListNotSelected is

SELECT Table5.EquipmentType, Table5.EquipmentTypeID, Table5.User, Table5.Computer, Table5.Selected FROM Table5 WHERE (((Table5.User)=mnu_fOSUserName()) AND ((Table5.Computer)=fOSMachineName()) AND ((Table5.Selected)=False)); 
 

and that for ListSelected is identical except that the last word becomes True.

See how you get on before I write the next chapter. I think War & Peace was shorter.

Phil

[Hinson3]

Hey Phil, I'm getting stuck. My EmpEquip table is equal to your Table 5 and my ProdServ table has all of the services. I have modified your code but seem to be stuck.

Error - SelectedSet!EmpEquipID = !EmpEquipID is not in the collection. I think I need to connect the EmpEquip table however since you noted that the ID field should not be a PK how should I relate it? Also, is the EquipmentTypeID a long integer or Long text

. . here's what I have.
***********************************
Tables
MemberService – MSID (PK), Acct#, MBRFName, MBRLName, SignInDate, SignInTime
EmployeeProfile – EmpID, EmpNo (employee unique #) , EmpFName, EmpLName’ Computer
EmpEquip - EmpEquipID, User, Computer, Selected Y/N (No primary key)
ProdServ – PSID(PK), Product, Service, Descrip
ProdEmp – JTID(PK), MSID, EmpNo, PSID

Queries
SignIn - Acct#, MBRFName, MBRLName, SignInDate, SignInTime
MemberQueue View - Acct#, MBRName, SignInDate, SignInTime
WrapUp – all service fields

Forms
SignIn Frm (dataEntry)
MemberQueue View (Continuous)
WrapUp (Popup,Modal) – MBRF&Lname, Acct#, Available Service --- Selected Services, ASRNo (employee unique #) – Test that all required fields are populated.

************************

Option Compare Database
 
Private Sub Form_Open(Cancel As Integer)
 
     Dim ComputerName As String
     Dim UserName As String
     Dim MyDb As Database
     Dim SelectedSet As Recordset, ProdServSet As Recordset
     Dim SQLStg As String, SQLStg2 As String
 
     ComputerName = fOSMachineName
     UserName = Mnu_fOSUserName
 
     SQLStg = "SELECT * FROM EmpEquip "                ' Table of selected services
     SQLStg = SQLStg & "WHERE User = " & Chr$(34) & UserName & Chr$(34)
     SQLStg = SQLStg & " AND Computer = " & Chr$(34) & ComputerName & Chr$(34) & ";"
 
     Set MyDb = CurrentDb
     Set SelectedSet = MyDb.OpenRecordset(SQLStg)
     With SelectedSet
         Do Until .EOF
             .Delete
             .MoveNext
         Loop
         .Close
         Set SelectedSet = Nothing
     End With
 
     SQLStg2 = "SELECT * FROM ProdService;"       ' The table where "All the services are stored"
 
     Set ProdServSet = MyDb.OpenRecordset(SQLStg2)
     Set SelectedSet = MyDb.OpenRecordset(SQLStg)
 
     With ProdServSet
         Do Until .EOF
             SelectedSet.AddNew
             SelectedSet!EmpEquipID = !EmpEquipID
             SelectedSet!User = UserName
            SelectedSet!Computer = ComputerName
             SelectedSet!Selected = False
             SelectedSet.Update
             .MoveNext
         Loop
         .Close
         Set ProdServSet = Nothing
         SelectedSet.Close
         Set SelectedSet = Nothing
     End With
 End Sub
 

Much Thanks!

[zmbd]

Hinson3
Please follow the troubleshooting instructions located in POST2-Section-A of this thread
You will need to repeat the compile step until it compiles without error as it stops on the first error found with each cycle.

[PhilOfWalton]

There seems to be some confusion.
The ProdServ table is a sort of mould or template. That will only get altered when the company adds another product or service to the range.
Your EmpEquip table should be identical to the ProdServ table but with 3 additional fields User, Computer and Selected. The EmpEquipID which is a copy of the ProdID in this case is not unique.
So in effect, the EmpEquip is a personalised version of the ProdServ table, personalised because some of the record in it are unique to that user and that computer.
Once the "copy" of the ProdServ table has been made by opening WrapUp form (I assume that is the form with the 2 list boxes), the ProdServ table is no longer used.

[Hinson3]

You are right, I was confused but I think I have it now. I like the fact that the ProdService table is no longer used after selection.

And yes I agree, War & Peace may be quicker read. However I’m getting close and I thank you for your patience.

My form currently does the following:
Captures the UserName, ComputerName

Services Selected list - is dynamic (changes are based on selection)

Service Available list - displays the static list (only changes if we add/delete)
To select multiple items {Shift + Click]

I get the "Row Source for Service Selected"

SELECT EmpEquip.Service, EmpEquip.EmpEquipID, EmpEquip.User, EmpEquip.Computer, EmpEquip.Selected FROM EmpEquip WHERE (((EmpEquip.User)=mnu_fOSUserName()) AND ((EmpEquip.Computer)=fOSMachineName()) AND ((EmpEquip.Selected)=False));

However, "Row Source for Service Available" I'm not sure. You mentioned before that my table [EmpEquip] your Table 5 should be identical to [ProdService] with the exception of the 3 fields User, Computer and Selected. My ProdService table does not have the 3 fields. Shall I add them? Do I need to create a many to many relationship?

Getting close.

aam

[PhilOfWalton]

The ProdServ table is only a template for creating your EmpEquip table. Once your WrapUp form is opened, and the ProdServ table is copied, it plays no further part in the database.

I have tried to illustrate it with the following picture, but seem to have trouble getting a clear image.
On the top is the equivalent to your ProdServ table, and beneath that is my table 5 (your EmpEquip table) showing 2 users on 2 computers. The rowsource of your listbox filters this to the correct user on the correct computer.

The RowSource that you have shown should be for the "Available" list box.
The Selected listbox's RowSource should be identical other than the last word is True.

Phil

[NeoPa]

Hi Phil.

Unfortunately your capture is of a small picture in a big screen so cannot easily be seen.

FYI. In order to get a picture to show in a post you can surround the link with [ImgNoThumb]...[/ImgNoThumb] tags.

-Ade.

[PhilOfWalton]

Hi Ade

I knew the image was no good, but as I don't have a web page, I can't see how to use your method.

Anyway I have played with the image, and that should now be easily readable.

Phil

[Hinson3]

Thank you NeoPa and Phil for the image.

Phil, I am a bit confused again. At the beginning your instructions were

“Now on the form where the 2 list boxes appear, we need to create that users personal list of services. “

I created the form (WrapUp) with 2 list boxes Available Service and Selected Service.

Next, your instructions were

“Now create an unbound form with 2 listboxes on it. I call them ListNotSelected & ListSelected.”

Is this an entirely new form?

[NeoPa]

No worries Phil. You just type it in as you would the text.

If you edit one that has it updated you'll see exactly what I mean.

[Hinson3]

NeoPa, was the previous reply for me? Hinson3?

[NeoPa]

No sir.

The Phil in the address was that of PhilOfWalton. It hadn't occurred to me that you may also be a Phil behind your user name. He'd had trouble getting his picture to show in the thread itself.

I updated his post to show the picture, and tried to explain how he could do the same for himself. He's a friend so I may give him a call to explain it if he continues to struggle.

As far as the question goes I'm happy to leave you in his very capable hands.

[PhilOfWalton]

Confusion, confusion.
Your WrapUp form is the what I an referring to, and what I referred to as "ListNotSelected", you have called "ServiceAvailable" and what I called "ListSelected" you called "ServiceSelected". We will use your words in future.

So I gave you the code to use on the OnOpen of your WrapUp form to create the portion of the EmpEquip table for that user and computer (you will have to modify the field names to suit your tables) and I also gave you the RowSource for the 2 list boxes.

Let me know when that is working OK.

Phil

[Hinson3]

Hello Phil:-( I’m not as skilled in Access as I thought. I recreated the database with only the two tables. (See attached) I tried everything I could do and research to get the Record Source for the “Service Available” list box to work. I activated the Multi Select to Extended and switched the Row Source Type from Table/query to Value List to Field List. It displayed the field names or #Delete never the table value or content. Normally I create ListBoxes using the wizard however I cannot do that with an unbound listbox nor can I get the checkboxes (on the table) to display in the ListBox.
I updated the code and got rid of the debug prompts and have attached a snapshot of my table and record source for ProdService. I’m stuck.

[PhilOfWalton]

I think you've struggled long enough.

Have a look at the attached.

Phil

[Hinson3]

Mercy! You are a prince, thank you Phil. However, when I opened the DB and double-click the form I got a Run-time error 3022. "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. When I click debug "SelectedSet.Update" is highlighted.

I modified the foreign key in the EmpCardService table to Yes (Duplicates OK) and it worked. Will this come back to bite me later?

[PhilOfWalton]

Sorry, missed that one. Duplicates OK is fine, but you may have to create a combined unique key of ProdServID, User & Computer as some stage.
See how it goes.

Incidentally, you should now see entries from my computer and your computer in the EmpCardService table.

Phil

[Hinson3]

Hello again! I have dropped the selected services list boxes into my database and are mapping the fields. Yayeee! Ran into a Compile error:

"The code in this project must be updated for use on 64-bit systems.
Please review and update Declare statements and then mark them with the PtrSafe attribute. Is there a vba code that test for 32-bit and 64-bit?"

I'm looking for a code that will test for both as some employees now have a 64-bit processor while most are still with the 32-bit.

Hmm, should this be on a separate post?

Hinson3

[PhilOfWalton]

I think start a separate post, as I have limited knowlege on this.

Be aware that windows can be either 32 or 64 bit, and Access can be either 32 bit or 64 bit.

[zmbd]

> Keep in mind, most Office installations are 32bit - The processor type will not matter, it's the office version that may be the issue:
- 32 bit installs are still the recommend default installation
- once a 64bit version of Office touches the file, the 32bit versions will most likely not be able to open the file.

> This article goes into a bit of detail and has an example of how to handle the 32/64 issue:

Compatibility Between the 32-bit and 64-bit Versions of Office 2010
I suspect it's the:

Private Declare Function apiGetComputerName Lib "kernel32" Alias _
     "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

giving the compiler heartburn.

and yes, this may be straying a tad from topic so it may be best to start a new thread if the above doesn't solve the issue(s)... just place a link in the new thread to this one for context :)

Choose the 32-bit or 64-bit version of Office (read more?)
(...)Note: The 32-bit version of Office works well with both the 32-bit and the 64-bit versions of Windows. If you are installing the 64-bit version of Office, you need the 64-bit version of Windows. If you’re not installing on Windows, you don’t need to worry about 32-bit or 64-bit options. (...)

[Hinson3]

Thanks guys. I moved my database with the two list boxes back to the laptop with the 32-bit version of Windows. I'm not willing to go there either. I am incorporating the dual listbox selections into the main data. Will keep you posted.

[zmbd]

To clarify, it's not the WINDOWS OS version that should be the issue it is the OFFICE/Access installation.

In fact, I run OFFICE2013(32bit) installations on my home pc under Windows8.1(64Bit) and at work under Windows7Enterprise(64Bit) without any issues.

The ONLY time I've ever encountered the ""The code in this project must be updated for use on 64-bit systems. (...)"" error message was on a OFFICE 64bit install which we promptly un-installed and re-installed 32bit OFFICE version because the user didn't need the additional features of the 64bit version as described in the link provide in my last post. The reason my friend installed the 64bit OFFICE version was because he thought that because the OS was 64Bit then the Office install should be too, frankly, that was my first thought too until I started researching the issue for him.

Frankly, I would simply alter the api call for the PC name to use the ptrSafe - hasn't caused any issues on my 32bit installations; however, you can use the contional #IF... #END IF construct as shown in the link just to be sure there are no issues.

Private Declare PtrSafe Function apiGetComputerName Lib "kernel32" Alias _
     "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

[PhilOfWalton]

I presume you are aware of Compiler Directives such as

    #If VBA7 Then
      Private Declare PtrSafe Function WinAPISetFocus Lib "user32" Alias "SetFocus" (ByVal hwnd As LongPtr) As Long
    #Else
      Private Declare Function WinAPISetFocus Lib "user32" Alias "SetFocus" (ByVal hwnd As Long) As Long
    #End If
 

This uses the correct API depending on the Access verssion being used.

There are similar Compiler directives depending on which version of windows is used.
I guess that currently, most users are on Windows 7 or 10 which, is available in both 32 & 64 bit, but providing you have a reasonably up-to-date machine will probably be running 64 bit.

I may be wrong, interested to hear other expert's views, but I seem to get away with either version of an API call without using the compiler directive and everything seems to work

Phil

[zmbd]

I've used the PtrSafe in the Windows-OS 64-Bit with OFFICE-32bit without issues; HOWEVER, I have heard/read about issues with using the the PtrSafe in Windows-OS 32-Bit installs - hence why I suspect the that that the compiler directives are covered in the link provided in Post#29 to the 32/64-bit compatibility and once again alluded to in Post#31

( ...more... ) This data type and the new PtrSafe attribute enable you to use this Declare statement on either 32-bit or 64-bit systems. The PtrSafe attribute indicates to the VBA compiler that the Declare statement is targeted for the 64-bit version of Office 2010. Without this attribute, using the Declare statement in a 64-bit system will result in a compile-time error. Note that the PtrSafe attribute is optional on the 32-bit version of Office 2010. This enables existing Declare statements to work as they always have. (...)

[Hinson3]

As far as getting the computername: I tried the following code:

If VBA7 Then
    Public Declare PtrSafe Function GetComputerName Lib "kernel32" _
        Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Else
    Public Declare Function GetComputerName Lib "kernel32" _
        Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
End If
 

It did not work, so I’m going to put it on the back burner for now as I need to get the 2 listboxes to work. Most of our users are on Office 2010 anyway, unfortunately, my office suite was upgraded to 2013 but I still have an Office 2010 desktop, which is what I will use going forward. I appreciate you responding even though it’s a topic for another forum. I’m learning the rules.

2 ListBox
Phil, First, I’m trying to understand exactly how the “2 List box” will work within the WrapUp form so that I can create a relationship. I’m trying to insert the two listboxes into the WrapUp form; it has all the data related to the current record. i.e. (Date, time, queue#, services, names etc). So, I’m trying to relate the WrapUp form with EmpPCandService or tbl_productService table. Since the EMPCandService table only has a foreign key, I cannot do a one to many and I expect to have 1 member or guest (record) to many services. Which is the sole purpose for the 2ListBoxes form.

Secondly, Right now, both the EmpPCandService table and tbl_ProductService displays all 95 services. When I open the TwoListBox form and make a selection – it does Check the selection(s). Am I to create a filter to show only checked services?

Original thought. . .
Was to have a WrapUp form that provided a summary of everything about the current record. It also allows the employee to modify and/or populate any required fields. Then, use the 2ListBoxes to add all the services they provided in one window. Before, they were in a continuous form and had to click an Add Record button that copied the current record Account#, Name and allowed them to add the additional service. They had to click that button for each service. I’m trying to work smarter.

Thank you again for you help.

[zmbd]

You Have To Use The Hash-Tag-IF #IF Construct

Please read the link I provided, it covers this in great detail.

[Hinson3]

Thanks ZMBD, I did try the #F and still got an error. I will try again tomorrow and report the message I get.

Thanks again.

[zmbd]

" #F " typo? We can fix that :)

Where in the module did you place the #IF construct?
It must be the first thing after the Option statements and it does not go in a procedure.

If you post that part of your code we can take a look, given that we're trying to solve the primary question this is still marginally on topic :)