Replies

Thanks again for your replies.

Larry.. thank you for your suggestion..I did try it but it did not solve the error message.

Wendell.. Good idea… I will make the text box a combo box…I had not thought about the fact that you can type in a combo box.. (I was focused on the LONG drop down list and not making someone scroll through it)..Thanks

John… I probably am making this more complex than it has to be.. but I can not think I can simplify in the way that you have suggested.

A little more detail to explain why.

We use this form to
create a unique job number to fulfill line_items on orders.
Some line items have a quantity greater than one so we need more than one job number for those line items.

the form is based on

tbl_job_Control_number

Which has many_to_one relationship to tbl_orders_details which of course has a many to one relationship to tbl_orders.

The combo box I want to filter ON exists to let the us assign a job number to every line_item (order_detail_id) on an order.. sometimes a line item may have a quantity of more than 1.. so that line item would need to have more than one jobs created to fulfill the order.

This assignment of a job_control_number_id to Order_detail_id ties our fabrication system to our order taking system.

The bound column is the order_detail_id and the second column is a concatenation of “line” & [oder_detail_id] & ” from order ” & [order_id]

So on the form the user selects from a list that reads like this item 8945 from order 387

I want them to be able to filter the records on the form by an order number.

So I in the former textbox now to be a combo box that lets call it cboFitler_criteria.. I want to select an order number and have the form return only those records that are jobs being made for that order.
If the order 317 had 3 line items on it Item A,B and C with quantities 1 of A, 2 of B and 5 of C then the filter would return 8 records.

Basing the whole for on a querry that included all three tables joined and the right fields included would let me have an order field on my form and then I could just filter on that…. BUT the query will not allow records to be edited . which is why I am trying to do it with the unbound column of the combo box…. (and a LIKE statement with wild cards) like this.

Me.Filter = [cbo42].[Column](1) Like “‘%” & Me.cboFitler_criteria & “%'”

But that cboFitler_criteria seems to render (or resolve not sure of the proper vocab) to NULL.

Whew… Hope that makes it more clear of why I am making it so complicated. And why I am trying it this way. Any more thoughts.

Thanks again so much for your help.

Jason

Thank you so much for your replies.

What I am doing is similar to the “use a combo box to filter records on a form” But I am using typed in text instead of a drop down selection… so I have a button to trigger it instead of the onchange event (although I guess I could use an onenter event or something like that)…

The idea is similar

The idea
1) an unbound control to enter the data to filter FOR

2) a field in the record list to filter ON

In the case that seems more nomal and to which you I believe you are refering…

1) you select what you want to filter FOR in an unbound combo box drop down list (probably in the former header or footer)

2)you filter ON a certain field in your form to find matches

In my case

1) we want to filter FOR what you enter in an unbound text box (in the form footer)

2) we want to filter ON a certain field in the record list BUT that field happens to be a combo box

AND not only is it a combo box but I want to inspect (and filter ON) the second column of the combo box for matches. NOT the bound column.

I hope that helps explain what I am trying to do a little bit more clearly. Thanks!!

Jason

Hello.

I am now trying this.x on the form

txt_orderinfo
its data source is =[cbo42].[Column](1)

The text box shows column 2 of my cbo42 combo box
This works properly

So now txt_orderinfo holds the the data I want to be examined and matched by the filter.

I then have an unbound textbox in the form footer where you can enter the text you want to filter for
And a Button you click with this CODE

Private Sub cmd_ordernum_Click()
Me.FilterOn = False
Me.Filter = Me.txt_orderinfo Like “‘%” & Me.txt_ordernum & “%'”
Me.FilterOn = True
End Sub

clicking produces
runtime error ’94’
Invalid use of null
In debug window…Hovering over it…

Me.txt_orderinfo resolves as NULL

Any thought would be greatly appreciated.

Thank you

Jason

Ian.

That did it. Thanks. I had an extra set of single quotes making it be evaluated as text?

Thanks very much, again.

Jason Gustafson

Hans.

Thank you. Awesome, as always.. Gonna take a bit to try this out. Ill post back when I do. Thanks again.

Jason

Thank Hans.

The normal usage is daily around lunch and around office closing.. but it is not reliable that it will get used daily…or at any specific time.

I think using the windows scheduler and the .bat to open the .mdb would work fine as trigger.

Once we have the trigger triggering right…

I have no clear idea on how to create the query that checks for null and sends an email if it finds null.

Thanks

Jason

Thanks Hans.

It is not open all the time, but possibly we could keep it open.

Or run a .bat to open it?

Thanks.

Jason

Hans.

Thank you!! As always you are on it. It works. Thanks again.

Jason

Hans.

thanks for the reply.

? CurrentDb.TableDefs(“tblCustomers”).Connect
does return the DNS string. It contains P99 (thought the DNS defined on this PC is for ZEBRAserver.com)

Tried it again after using the linked tables manager to refresh the tables links ..
? CurrentDb.TableDefs(“tblCustomers”).Connect
returns the DNS string… still with P99
Though the tables connect so the mdb is using zebraserver.com (somehow)

Tried it again after Compress and Repair. Tables connect fine but the command still returns P99

Any more thoughts.

Thanks

Jason

Hans.

Thanks. Got it working…Great solution help for my mediocre table design.

Should I have designed my tables another way?
tbl_products
tlb_products_price_affecting_aspects
tbl_products_to_products_affecting_aspects

Or some better way?

Thanks again.

Jason

Thank you Hans.

The mdb is attached (stripped and zipped)

In factors table I added a field to roughly describe what that factor is to be used for.

Thanks

Indeed it does work perfectly.

Thank you very much Hans.

Very truly yours.

Jason

Thank Hans.

If I were to create say 4 list boxes. Each to display 25 items.
How could I tweak the list box row source property to do that
and
How could the allenbrowne code be tweaked to pass any and all values selected in any and all of the textboxes to the report query?

Thanks.

Jason

Thank you Hans. As always. Your instructions worked perfectly.

I used the following and it worked.
placed image file in same folder as MDB. Named image back.jpg
Then used this code.

Private Sub Report_Open(Cancel As Integer)

Me.Picture = CurrentProject.path & “back.jpg”

End Sub ‘Form_Open(Cancel As Integer)

You are so very GOOD my friend.

On first test, your code worked perfectly on my test data. Will play more tomorrow on various situations.

Thank you Hans. I truly appreciate it.

Regards.

Jason