text box that look up value from another table than the forms record source (1 Viewer)

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
One thing you can try first is:
Code:
=DMax("CLng(Nz(antalemnerproduceret, 0))";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

just in case the #Error was caused by Null values for field [antalemnerproduceret]. Again, untested!
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
OK, glad it works for you.

*** BUT ***
Double check your results - numbers stored as strings sort differently.
E.g Test in Immediate Window (Ctrl+G)
Code:
?"1000" > "99"
False
Surely 1000 > 99 so we would expect the result to be True!!!

Perhaps DMax() makes an implicit conversion, but I doubt it (though I haven't tested).

You may need to create a query based on tbl_tidstabel that casts the string as a numeric, and base your DMax() on that query instead.
Eg qry_tidstabel
Code:
SELECT [No_], CLng(antalemnerproduceret) AS antalemnerproduceret_nbr FROM tbl_tidstabel;

Then,
=DMax("antalemnerproduceret_nbr";"qry_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

hth,

d
Haven't been able to force an error yet after some testing, so this must OK for now :)

The above issue is part of a little time clock database that I am doing at work. Its purpose is to keep track of which productionsorders are beeing worked on. Now.. this i work in progress and and feature requests from colleagues keep coming faster than I can implement them :)
So... I have another issues which might belong i another thread. I don't know. Anyhow here it comes.
Right now I have a continuous form [frm_ctn_List] which record source is a query showing production related stuff (Item no., Description, quality, costumer etc.) Clicking a specific production order no. opens up form [frm_ctn_TidsSkemaPanelfilter]. The production order no. is passed to this form. This form has table [tbl_TidsTabel] as record source. When the employee fill out this form they "Start" the job and select employee from a combo box. The information is saved in the table, and when the form is closed an append query updates a Yes/No field in a status table [frm_ctn_Status] to Yes, indicating that the job has started. Conditional formatting colours a textbox based on this yes/No field. From the form [frm_ctn_TidsSkemaPanelfilter] the employee open form [frm_ctn_Status] from where they can complete/finish the job. This is a Yes/No check box which filter out this specific productionorder from the continuous form [frm_ctn_List].
So to sum up... as of now the employee can start and stop a production order job. When the job is started by any employee a box turn blue indication the job is started. When finished the job disappear from the form [frm_ctn_List]. That's it!
Now I would like make visible (live) which employee are working on which order. An order can be started but that doesn't mean that anybody is working on it. They could have stopped the job and started another one. This isnt visible at the moment.
I cant figure out if I have enough information in my table [tbl_TidsTabel] to accomplish this.
A record is added to this table [tbl_TidsTabel] every time a employee start or stop a job. So start and stop aren't stored i the same record.
Well that became a little longer than intended. Hopes it makes sense.
 

Attachments

  • tbl_TidsTabel.png
    tbl_TidsTabel.png
    38 KB · Views: 29
  • frm_ctn_Status.png
    frm_ctn_Status.png
    4.8 KB · Views: 25
  • frm_ctn_TidsSkemaPanelfilter.png
    frm_ctn_TidsSkemaPanelfilter.png
    14.4 KB · Views: 25
  • frm_ctn_List.png
    frm_ctn_List.png
    63.9 KB · Views: 24

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
Haven't been able to force an error yet after some testing, so this must OK for now :)
Please do a small test for me: Run a query using this SQL:
Code:
SELECT
  [No_],
  antalemnerproduceret
FROM tbl_tidstabel
WHERE Len(antalemnerproduceret & '') > 0
ORDER BY
  [No_],
  antalemnerproduceret
;
Post a screenshot of the result here (like your screenshot of tbl_TidsTabel in you previous post)

With regards to your other question, it is probably better asked in new thread. I guess you can use Conditional Formatting.
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
Please do a small test for me: Run a query using this SQL:
Code:
SELECT
  [No_],
  antalemnerproduceret
FROM tbl_tidstabel
WHERE Len(antalemnerproduceret & '') > 0
ORDER BY
  [No_],
  antalemnerproduceret
;
Post a screenshot of the result here (like your screenshot of tbl_TidsTabel in you previous post)

With regards to your other question, it is probably better asked in new thread. I guess you can use Conditional Formatting.
Image attached :)
 

Attachments

  • tbl_TidsTabel.png
    tbl_TidsTabel.png
    15.3 KB · Views: 21

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
Yes, it looks like you haven't encountered incorrect results just through luck!

For instance, with the record where No_ = 570258 and antalemnerproduceret = '800', if you were to change '800' to '80', see if your DMax() formula gives the result you expect.

It would be worth trying to get the revised DMax() expression in post #21 working.

Try it like:
Code:
=DMax("CLng(Nz([antalemnerproduceret], 0))";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
I have 3 records with the same production order number. If I change for example 800 to 80, the value on the form change to 700, which is the correct behaviour.
 

Attachments

  • Table.png
    Table.png
    4.1 KB · Views: 19
  • Form.png
    Form.png
    3.1 KB · Views: 21

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
OK, great!

Perhaps it works differently in VBA than when used through a query. I only did a quick test in VBA, not using a query expression.

What happens if you run query (with the new value antalemnerproduceret = '80'):
Code:
SELECT
  [No_],
  MAX(antalemnerproduceret) AS Test
FROM tbl_tidstabel
WHERE [No_] = '570258'
GROUP BY
  [No_]
;

If it works and you are happy then mission accomplished! 👍
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
Like this :)
 

Attachments

  • Table80Value.png
    Table80Value.png
    3.6 KB · Views: 18

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
Sorry, I needed more coffee! :coffee:

I was getting confused between which field was text and which field was number! 😲

Of course, antalemnerproduceret is a numeric field in your table (it is [No_] that is the string 😖 ), so the DMax/Max sorting will work correctly.
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
I know what you mean :)
So know that you are fueled up with coffee maybe you could guide me in a query issue. :)
I have a reservation table "dbo_KonfAir DRIFT$Reservation Entry" from where I need to filter out.
Entry No_: need to be distinct but is shown in pairs
Source Type "37" and "5406" needs to be in separate columns
The corresponding "Source ID also needs to be in separate columns
I also need the describtion field but the values here are always identical.
See attached image. Top is source - Bottom result
I have something like this but it doesn't seem to work
Code:
select
   tabA.Entry_NO,
   tabA.Reservation,
   tabA.[Source Type] as "Source Type_ProductionOrder],
   tabB.[Source Type" as "Source Type_SalesOrder],
   tabA.[Source ID" as "Source ID_ProductionOrder],
   tabB."Source ID" as "Source ID_SalesOrder"
from
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
where
         tabA.Entry_NO_ = B.Entry_NO_
  and tabA.Entry_NO_ = 33206611
  and tabA."Source Type" = 37
  and tabB."Source Type" = 5406
 

Attachments

  • ReservationEntry.png
    ReservationEntry.png
    266.1 KB · Views: 25

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
Code:
select
   tabA.Entry_NO,
   tabA.Reservation,
   tabA.[Source Type] as [Source Type_ProductionOrder],
   tabB.[Source Type] as [Source Type_SalesOrder],
   tabA.[Source ID] as [Source ID_ProductionOrder],
   tabB.[Source ID] as [Source ID_SalesOrder"
from
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
where tabA.Entry_NO_ = B.Entry_NO_
  and tabA.Entry_NO_ = 33206611
  and tabA."Source Type" = 37
  and tabB."Source Type" = 5406

You missed replacing a few double quotes with square brackets.

If tabA.Entry_NO_ is a string datatype then surround 33206611 with single quotes.
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
I see :)
Entry_NO is an autonumber
Though Changing to Brackets I still get an error. See attached.
Also I would like to run through all records
 

Attachments

  • Errorpromt.png
    Errorpromt.png
    12.7 KB · Views: 20
  • tbl_designview.png
    tbl_designview.png
    138.4 KB · Views: 23

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
Though Changing to Brackets I still get an error. See attached.
Forgot to make the changes in the WHERE clause!
Code:
SELECT
   tabA.Entry_NO,
   tabA.Reservation,
   tabA.[Source Type] as [Source Type_ProductionOrder],
   tabB.[Source Type] as [Source Type_SalesOrder],
   tabA.[Source ID] as [Source ID_ProductionOrder],
   tabB.[Source ID] as [Source ID_SalesOrder"
FROM
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
WHERE tabA.Entry_NO_ = B.Entry_NO_
  and tabA.Entry_NO_ = 33206611
  and tabA.[Source Type] = 37
  and tabB.[Source Type] = 5406
;

Also I would like to run through all records
Please explain what you mean by this
 

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
Also, have you got field Entry_NO correct?

Sometimes you have a trailing underscore, other times not.

In the screenshot of the table it contains a space, but not in your SQL

Which is correct???
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
The sql below give an error. Image attached.
Maybe the sql is wrong build compared to what I would like to accomplish (Try looking at the attached image Query.png)
"Entry No_" come in pairs in this table. The information in the 2 paired records should be populated in to one record for each pair.
The criteria is that "Source Type" is either "37" or "5406"
Hope it makes sense :)
Now I think I need a coffee refill


Code:
SELECT
   tabA.Entry No_,
   tabA.Reservation,
   tabA.[Source Type] as [Source Type_ProductionOrder],
   tabB.[Source Type] as [Source Type_SalesOrder],
   tabA.[Source ID] as [Source ID_ProductionOrder],
   tabB.[Source ID] as [Source ID_SalesOrder]
FROM
   [dbo_KonfAir DRIFTSReservation Entry] tabA,
   [dbo_KonfAir DRIFTSReservation Entry] tabB
WHERE tabA.Entry No_ = B.Entry No_
  and tabA.Entry No_ = 33206611
  and tabA.[Source Type] = 37
  and tabB.[Source Type] = 5406
;
 

Attachments

  • Error.png
    Error.png
    11 KB · Views: 30
  • Query.png
    Query.png
    95.1 KB · Views: 28

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
You must use the square brackets around field names with spaces in.

Yes, the SQL as you have it will not give the result you want.

Will the [Entry No_]/[Item No_] combinations always come in pairs?
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
[Entry No_] always comes in pairs and the corresponding [Item No_] will also be the same 2 values... But a specific value in [Item No_] can be represended multiple times.
 

cheekybuddha

AWF VIP
Local time
Today, 20:38
Joined
Jul 21, 2014
Messages
1,012
It's not clear what determines which of the two records belong to ProductionOrder and which to SalesOrder.

Will [Source Type] = 37 always mean ProductionOrder and [Source Type] = 5406 always mean SalesOrder?

If not, what determines which record is which?
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
Actually it’s the other way around
37 is the sales order table number and
5404 is the production order table number.
That’s always stay the same.
The reason for this query is to find which production order comes from which sales order.
The only place in our ERP system where they are linked together is in this reservation table.
Hope this makes sense
In the ERP system Microsoft Dynamics NAV we can order track our production orders but when used as linked tables in Access I have to make the connection in this query
 

zulu100

Member
Local time
Today, 21:38
Joined
Nov 3, 2021
Messages
54
In the illustration image I made 570260 is the production order number coming from sales order number 90394
 

Users who are viewing this thread

Top Bottom