Binding query to textbox

[olxx]

Hi,

Here´s my problem:
I have working form of Invoices (linked to tblInvoices) and I want the InvoiceNr to be checked if it exists in tblSomeTable. The check should be performed every time i move to next record on form (next invoice). If the invoice exists the text (txtbox or label, not msgbox) on the form should inform the user, ok or not. Please help.

olxx

 

[namliam]

Why do this on form? Why not make an outer join in a query on your invoice number and use that to identify your "ok or not" thing....

 

[olxx]

Thx, for trying to help!
Ok. but how can i get that query value on my form? To run query (e.g using commandbutton) each time to check takes time and is frustrating. What i want is when i scroll through invoices or open just one of them, to see the "ok or not" text next to the invoice nr on my form.

 

[namliam]

No no no...

You run 1 query that leads into your form
In your query you can make a new field which says "ok" or "not" as you desire using some construction involving IIF and Isnull...

 

[olxx]

I still don´t get it.
Right now I have form, two tables (tblInvoices, tblInvoiceDetails) and query that binds two tables on the form ( to show total and taxes etc.). I got the idea from Northwind db. It works fine. Now the checking involves the other table that is not (and can´t be) related. I think if i try bind the other table to the query that i´m using right now, it will mess it up.

 

[namliam]

Why should it mess it up??
Simply (outer) join it to your invoice (details) table and you should be good to go...

 

[olxx]

Ok. How do I outer join in it? If i add just that one field from another table to that query and if query won´t find match, it doesn´t result me any fields. I could send you the db, but it´s in foreign language and there are much more tables, forms and queries involved. I´m starting to lose hope..

 

[namliam]

dont lose hope...

If you "join" there is a line between the tables, yes??
Double click said line and pick the option (1 is currently select, thus leaving 2 or 3) you want. This will allow for the record to show up wihtout a match.
This is called an Outer join

 

[olxx]

Yep i got it, tried it, but it´s not what i want. With outerjoin query gives me the records that match but i need query to give me all records and an extra field for each record showing "ok" or "not ok". So i can place that extra field to my form. Point is, that my form is not linked directly table but query and then tables fields.

 

[namliam]

Huh?? Dont be so quick to dismiss something !

Yes you want this... but you want it one step beyond this...
YourOkNotField: IIF(Isnull(YourTable.Yourfield), "NOT", "OK" )
YourOkNotField is the field you want to add having the OK/NOT value
Yourtable.Yourfield is the extra field you have now in the query from the outer join.

Linking a form (having its rowsource) a query vs table doesnt make any difference in the form's performance or functionality.

 

[olxx]

I´m making simplified sample db right now in english, i´m almost done, so if you wait i´ll post it soon somehow.

 

[olxx]

OK. here´s the file, see if you can get the thing.

 

[olxx]

I have to go offline now. Will be back tomorrow morning or maybe in the evening, thanks for trying to help Mailman!

 

[namliam]

Sorry cannot open A2007 dbs

 

[olxx]

Ok. here´s the 2002-2003 version.

 

[namliam]

Like i said a simple IIF... Use below sql for your qInvoices

SELECT invoices.[Invoice nr]
, invoices.[someField 1]
, invoices.someField2
, invoices.ID
, IIf(IsNull([tabeltocheckfrom].[Invoice nr]),"NOT","OK") AS YourNewField
FROM TabelToCheckFrom 
RIGHT JOIN invoices ON TabelToCheckFrom.[Invoice nr] = invoices.[Invoice nr];

That will do what you want...

<insert rant about using spaces and using a naming convention>

 

[olxx]

Wow, it works!! I don´t understand really how (i don´t know SQL that well). But BIG THANKS Mailman!!! I guess i have to study that IIf clause a bit more and that joining thing. Again Thank You very much for your help.