List box will not load correctly

[aaronb50]

When I use:

If .Fields("Nomenclature") = "" & a & "" Then
 
  Me.List2.AddItem (.Fields("Nomenclature") & ";" & .Fields("MaterialNumber") & ";" & .Fields("LogNumber") & ";" & .Fields("CreateDate"))

Each record correctly finds it way to the list box.

But when I use:

If .Fields("Nomenclature") Like "*" & a & "*" Then
   
     Me.List2.AddItem (.Fields("Nomenclature") & ";" & .Fields("MaterialNumber") & ";" & .Fields("LogNumber") & ";" & .Fields("CreateDate"))

The records all scrambeled in the list box. I have Part Numbers in the Date column, Dates in the Log Number column, its just all over the place.

Why is this happening?

 

[aaronb50]

I didn't make any changes to the AddItem line.

 

[CJ_London]

you probably need to add another semi colon to your additem line

 

[aaronb50]

Where would I put it?

That line works just fine if I'm looking for an exact match to the value of a.

It's when I try to use "Like a" that it gets all messed up.

 

[CJ_London]

well you've not provided much information, but your listbox rowsource will be a valuelist that should look like

a1;b1;c1;d1;a2;b2;c2;d2;.....

I suspect you are getting something like

a1;b1;c1;d1a2;b2;c2;d2;.....

alternatively, whatever is being returned includes a null

copy and paste to this thread the rowsource once it is 'populated correctly' and again when it is incorrect. Assuming you are using 2007 or later, you can change the form view to layout, right click on the listbox and select properties - you'll see the rowsource under the data tab

 

[aaronb50]

Didn't realize I could do that. lol

Ok when looking for an exact match I get:

SCREW;19M7797;SM170007;1/9/2017 This is correct.

It only finds one item with "Screw" as the description.

But when I look for descriptions that have the word "Screw" anywhere in the description I get:

MACHINE SCREW;PHILLIPS PAN HD;#4-40 X 3/4";MS35206-219;SCREW;FLAT HEAD;100 DEG;# 8-32 X 2.00;SCREW;LOCK ASSY;M24308/25-9F;BC160202;SCREWDRIVER;TORQUE;ADJ;3-15 IN LB;CAPSCREW;SOCKET HD;M6 X 20;92235A240;MACHINE SCREW;FLAT COUNTERSUNK HD;10-32 X 3/4;MS24693-C274;CAPSCREW;SOCKET HEX HD;10-32 X 1;MS16996-14;TORQUE SCREWDRIVER;7-36
This is not correct. I should have Description, Part Number, Log Number and Date on each record.

 

[CJ_London]

none of those have dates - temporarily change the column count to 3 and see what it looks like.

Also, this seems a complex way of doing something that is simple - can you explain what you are trying to do - and are you using dao or ado?

 

[aaronb50]

This is with 3:
MACHINE SCREW;PHILLIPS PAN HD;#4-40 X 3/4";SCREW;FLAT HEAD;100 DEG;SCREW;LOCK ASSY;M24308/25-9F;SCREWDRIVER;TORQUE;ADJ;CAPSCREW;SOCKET HD;M6 X 20;MACHINE SCREW;FLAT COUNTERSUNK HD;10-32 X 3/4;CAPSCREW;SOCKET HEX HD;10-32 X 1;TORQUE SCREWDRIVER;7-36 IN/LB;J6106A;CAPSCREW;SOCKET HEX HD;8-32 X 1/2;CAPSCREW;SOCKET HEX HD;8-32 X 3/4;MACHINE SCREW;FLAT COUNTERSUNK HD;8-32 X 5/8 (CADMIUM PLATED) (SNOWBIRD ECU);MACHINE SCREW;FLT CSUNK PHILLIPS HD;100 DEG;SOCKET HEAD CAP SCREWS 6-32X1;LXJ980;BC170096;SOCKET HEAD CAP SCREWS 8-32X1-1/4;LXJ986;BC170096;SOCKET HEAD CAP SCREWS 8-32X1;LXJ985;BC170096;MACHINE SCREW;PHILLIPS PAN HD;4-40 X 3/8";CAPTIVE SCREW;UNFINSHED;#10-32;CAPSCREW;SOCKET HEX HD;10-32 X 1;MACHINE SCREW;PHILLIPS PAN HD;8-36 X 3/4;1-5/8" DRYWALL SCREWS 100 PK;3JHH4;DK160007;RA Screwdriver Kit;722PR404;DK170011;SCREW;FLAT HD;SOCKET CAP;SCREW;FLAT HD;SOCKET CAP;4mm Hex Screwdriver Bit;1/4" Shank Size;42W667;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;SCREW;SOCKET HD CAP M8;GRADE 8;CAPSCREW;SOCKET HEX HD;4-40 X 3/8;SCREW;FLAT HD;SOCKET CAP;CAPSCREW;SOCKET HD;M6 X 20;CAPSCREW;SOCKET HD;M6 X 20;CAPSCREW;SOCKET HD;M6 X 20;CAPSCREW;SOCKET HD;M6 X 20;CAPSCREW;SOCKET HD;M6 X 20;CAPSCREW;SOCKET HD;M6 X 20;CAPSCREW;SOCKET HEX HD;8-32 X 3/4;CAPSCREW;SOCKET HEX HD;8-32 X 3/4;SCREW;FLAT HD;SOCKET CAP;3/4" case hardened steel self tapping screws;4HJX5;JK170008;3/4" washers for 3/16" screws;35YU70;JK170010;3/4" SCREWS;1LY82;JK170017;35 Pcs Screwdriver Bit Set;20UH15;JK170024;11 IN 1 General Purpose Screwdriver;2RKT1;JK170024;Screwdriver;Torque;Adjustable;Xcelite multiple spline screwdriver plade;6 flute .060";272SC240;SCREW;8-32 SCK FH;NAS514P832-8B;SCREW;10-32 CSK FH;NAS514P1032-12B;MACHINE SCREW;FLAT HD;4-40 X 7/16 (BLACK OXIDE);MACHINE SCREW;FLAT HD;4-40 X 5/8 (BLACK OXIDE);MACHINE SCREW;PHILLIPS PAN HD;8-32 X 2;SCREW;SOCKET HD CAP M8;GRADE 8;SCREWDRIVER SET;OFFSET RATCHET;MULTI-BIT 18PC;SCREWDRIVER;TORQUED;1/4IN DRIVE 5-40;SCREWDRIVER;TORQUED;1/4IN DRIVE 5-40;SCREW;RETURNING;186-6278-03;SCREW;VENT;186-6293-01;SEAL;VENT SCREW;186-6289;SCREW;RETURNING;186-6278-03;SCREW;VENT;186-6293-01;SEAL;VENT SCREW;186-6289;SCREWS;DRYWALL;CARBON STEEL;SCREWDRIVER;OFFSET;1/4" #2;SCREWDRIVER;STEEL;FLATHEAD;SCREW;POLARIS;7518412;SCREWDRIVER;PHILLIPS HD #2;12IN;SCREWDRIVER;PHILLIPS HD #2;8IN;CAPTIVE SCREW;UNFINSHED;#10-32;SCREWDRIVER;PHILLIPS HD;#2 X 10;SCREWDRIVER;PHILLIPS HD X-LNG;RND SHANK #2;SCREWDRIVER;PHILLIPS HD;#2 X 10;SCREWDRIVER;PHILLIPS HD X-LNG;RND SHANK #2;CAPSCREW;SOCKET HEX HD;10-32 X 1 1/4;SCREW;CAP;SOCKET HEAD;HEXAGON SOCKET CAP SCREW;1/4-28;7/8 LENGTH;MACHINE SCREW;PHILLIPS FLAT HEAD 1/4-28X1 (PACK);1163898;CAP SCREW;19M7373;SM170007;SCREW;19M7797;SM170007;SCREW;SOCKET HD CAP;186-6050;SCREW;TAPING;11503617;1 1/4";CAP SCREW;HEX;SOCKET HEAD CAP SCREW;STAINLESS STEEL 3/8-16X1/2;MS16995-77;1" hardened steel self drilling tapping screw with hex washer head type and zinc plated finish;31JK16;TA170001;1-3/4"18-8 Stainless steel sheet metal screws with pan head type and plain finish;1VE65;TA170001;18-8 Stainless Steel Phillips Rounded Head Screws;M4 x 0.7 mm Thread;35 mm Long;18-8 Stainless Steel Phillips Rounded Head Screws;M4 x 0.7 mm Thread;40 mm Long;18-8 Stainless Steel Phillips Rounded Head Screws;M4 x 0.7 mm Thread;45 mm Long;1/2-20 INCH THREAD SCREW;85728228;WC160009;stainless steel washer for 1" screw size.;98125A038;WC160010;SCREW;1/2-20 THREAD;DRILLED;CLAMP;SCREW LOCK;M24308/25-10

I'm using dao.

I was just using SQL:


Me.List2.RowSource = "SELECT [Nomenclature], [Material Number], [Log Number], [Create Date] " & _
"FROM [tblMORsubtable] " & _
"WHERE Nomenclature Like '*" & a & "*' " & _
"ORDER BY [Create Date]"

Me.List2.Requery

But we are moving the program to Citrix and IT will not give the users the correct privileges. So I'm having to go back and change the code everywhere I was using SQL statements.

 

[aaronb50]

I cant use the SQL statements because I can not use linked tables.

 

[aaronb50]

I think I figured it out.

The first one it finds has commas in it. Its seeing the commas and braking up the description.

Actual description in the back end:
MACHINE SCREW, FLAT COUNTERSUNK HD, 10-32 X 3/4

its getting separated at the commas when it loads to the list box:
MACHINE SCREW;PHILLIPS PAN HD;#4-40 X 3/4

I'm going to have to replace them, and ' and " and probably other things before I place them in the list and then go back and put them back in there.

Ugh.................

 

[CJ_London]

i'm surprised the move to citrix is a problem - both front and back ends would be on citrix and you should still be able to have linked tables. I have a couple of clients using citrix and it has never been a problem.

So how is your recordset created since you are using

If .Fields("Nomenclature") = "" & a & "" Then

.fields implies a collection of a recordset

 

[MarkK]

A listbox has a recordset property. If you can open a recordset, just assign it to the listbox's recordset property, like...

set me.list2.recordset = currentdb.openrecordset( _
   "SELECT Nomenclature, MaterialNumber, LogNumber, CreateDate " & _
   "FROM tblMORsubtable " & _
   "WHERE Nomenclature Like '*" & a & "*' " & _
   "ORDER BY CreateDate "

...which is a much simpler way to populate the list than your loop. And you won't have to worry about delimiters.

 

[aaronb50]

Thank you Markk!!!

I was sure there was a way to do that but I didn't get an answer as to how when I posted the question.

CJ, IT gave me a path for linking the tables to the back end that started out with D:\.

When I go to the table manager to link to the back end on Citrix it cant the path starting with D:\Program\Program.accdb.

It works fine if I use the path that includes the share drive in the path name \\nhr-abc\Program\Program.acccdb.

But the program will not let users into the back end via the front end unless IT gives them special permissions to use the share drive and IT wont do that. I was told something about Citrix was not meant to have the share drive be part of a program.

I gave up...........

The good news is I can open a recordset using the D:\Program\Program.accdb.

And assuming I can assign a database to the SQL like Markk showed, I should not have to go back and re-code everything. Although I'm almost done but I'll revert back and change the SQL statements.


To open the record set, in a module I set a global path and then call it like this

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = BackEndDB1
Set rst = db.OpenRecordset("Drawings", dbOpenDynaset)