Query fill a box in a form automaticaly

[Pusher]

Hi all,

I need to enter a value in my table from a query that is conditional via my form automatically (combo box triggers a query). Is there a way to do this? I can fill a List box with my query but i can't make it go in a record. I actually can, but I must select the list box before it enters it in my table…and i want to make enter automatically and make the list box invisible.

I actually need to make my list box choose the only result (the query gives only one choice) that is in the list.

Thanks

 

[vbaInet]

What you are saying is you want to create a search form?

By the way, what is beogradput? A construction company you run?

 

[Pusher]

Not a search form, i have a form, it’s a form for entering other data in my table. I just want to enter a new field in my table that will be filled by a query that is conditional with another field value in the same table.
BeogradPut is a public utility company that runs the roads and transportation in Belgrade, Serbia

 

[vbaInet]

Alright. It's not good practice to save calculated values. All you do is calculate the value whenever you need it. Here's why you shouldn't save calculated values:

http://allenbrowne.com/casu-14.html

I see. So the company has a monopoly on that sector? You handle the IT functions?

 

[Pusher]

Actually I’m an IT engineer that they tasked with the making this db… self thought in access 2007, that’s why I’m full of questions And we are in a process of privatizing , that’s the main reason I’m doing this db.
Can I make a calculated field with a query? I’m using access 2007 so I don’t have a calculated option in my access

 

[vbaInet]

Most of us on here are self thought So you do more hardware then? Or web development?

Yep, just put the calculation in the query then you can re-use the query as many times as you wish.

 

[Pusher]

More in System Administration.

Where to put it so it calculate it, I have a finished query and everything but don’t know how to make that field calculate and fill it.

 

[vbaInet]

Put the calculation in a new column in the query.

 

[Pusher]

... I’ll figure it out In time... does this calculated filed is done just one time and when its entered in the db it does not change. The result of this query is different in time, that’s why I made this calculated field. Same records will have different query results in time. I need this for statistics. Also can I make this work in for old records (I have 3500 old records )

 

[vbaInet]

If you don't give it a try you will not know Try it out and see if it gives you the desired results.

The calculation will update when you change the value in the field. Just like how forms work when you perform calculations in controls.

 

[Pusher]

Now i have a problem if making a query within a query... a subquery... a subquery that uses the field in the first query... my head hurts...

 

[vbaInet]

Declare the data type in the Parameters dialog box in your query. Click the Parameters menu to open it up.

What is the problem?

 

[Pusher]

Can I give you my db for you to do it, I can’t find how I can do this. I only found simple operation Subqueries and I cant make any head way…

 

[Pusher]

The problem is that i cant connect the ID_RASKRSNICE and ID_UREDJAJ in subquery...
This is something like this

SELECT IZVESTAJ.*, (SELECT RASKRSNICA.ID_UREDJAJA
FROM UREDJAJ INNER JOIN (RASKRSNICA INNER JOIN IZVESTAJ ON RASKRSNICA.ID_RASKRSNICE = IZVESTAJ.ID_RASKRSNICE) ON UREDJAJ.ID_UREDJAJA = RASKRSNICA.ID_UREDJAJA
ORDER BY IZVESTAJ.ID_IZVESTAJ;) AS ID_UREDJAJA
FROM IZVESTAJ;

 

[rodmc]

Pusher, in a new column in your query (as vbaInet has said), then in that column invoke the expression builder!

 

[Pusher]

I get that, but how to make the new field in one record be the result of a query that is conditional from a different field in the same record...

 

[rodmc]

Use an IF statement as part of your expression

 

[vbaInet]

You haven't shown us the SQL that you tried which fails.

 

[Pusher]

This is the table that needs ID_UREDJAJA1 to be filled with the result of a query

[B]SELECT IZVESTAJ.*,[/B] (SELECT RASKRSNICA.ID_UREDJAJA
FROM UREDJAJ INNER JOIN (RASKRSNICA INNER JOIN IZVESTAJ ON RASKRSNICA.ID_RASKRSNICE = IZVESTAJ.ID_RASKRSNICE) ON UREDJAJ.ID_UREDJAJA = RASKRSNICA.ID_UREDJAJA
ORDER BY IZVESTAJ.ID_IZVESTAJ;) [B]AS ID_UREDJAJA1
FROM IZVESTAJ;[/B]

This is the subquery

SELECT RASKRSNICA.ID_UREDJAJA
FROM UREDJAJ INNER JOIN (RASKRSNICA INNER JOIN IZVESTAJ ON  [B]RASKRSNICA.ID_RASKRSNICE = IZVESTAJ.ID_RASKRSNICE[/B]) ON  [B]UREDJAJ.ID_UREDJAJA = RASKRSNICA.ID_UREDJAJA[/B]
ORDER BY IZVESTAJ.ID_IZVESTAJ;

this is the main join
RASKRSNICA.ID_RASKRSNICE = IZVESTAJ.ID_RASKRSNICE and UREDJAJ.ID_UREDJAJA = RASKRSNICA.ID_UREDJAJA, when i start the query i get 3500 results, for every record, i need just the one result for that one record i want to calculate... that is done by connecting the query with just that one record... i don't know how to do that

 

[rodmc]

This is the table that needs ID_UREDJAJA1 to be filled with the result of a query

[B]SELECT IZVESTAJ.*,[/B] (SELECT RASKRSNICA.ID_UREDJAJA
FROM UREDJAJ INNER JOIN (RASKRSNICA INNER JOIN IZVESTAJ ON RASKRSNICA.ID_RASKRSNICE = IZVESTAJ.ID_RASKRSNICE) ON UREDJAJ.ID_UREDJAJA = RASKRSNICA.ID_UREDJAJA
ORDER BY IZVESTAJ.ID_IZVESTAJ;) [B]AS ID_UREDJAJA1
FROM IZVESTAJ;[/B]

This is the subquery

SELECT RASKRSNICA.ID_UREDJAJA
FROM UREDJAJ INNER JOIN (RASKRSNICA INNER JOIN IZVESTAJ ON  [B]RASKRSNICA.ID_RASKRSNICE = IZVESTAJ.ID_RASKRSNICE[/B]) ON  [B]UREDJAJ.ID_UREDJAJA = RASKRSNICA.ID_UREDJAJA[/B]
ORDER BY IZVESTAJ.ID_IZVESTAJ;

this is the main join
RASKRSNICA.ID_RASKRSNICE = IZVESTAJ.ID_RASKRSNICE and UREDJAJ.ID_UREDJAJA = RASKRSNICA.ID_UREDJAJA, when i start the query i get 3500 results, for every record, i need just the one result for that one record i want to calculate... that is done by connecting the query with just that one record... i don't know how to do that

sounds like your getting a cartesian product, you need to re examine your query and get it down to one record BEFORE you apply any calculations

have you got a schema we could look at?