Add external variable into query 3

[ZOR]

I have a maktable query, gets data from another table. However I want to add the date value thats been entered in a textbox on the form. In other words for every record in the new table I want that textbox data to go in. Stuck!! Thanks

 

[Remou]

Have you tried:
[tt]SELECT tblTable.Field1, [blue][forms]![frmForm]![txtText][/blue] AS TheDate INTO tblMakeTable
FROM tblTable;[/tt]

 

[ZOR]

Thanks Remou, no I have not tried that, however I have moved in another direction which may end up a regret. I now have the date value put into the first of 40 records. I now need a way to put that value into the new tables field. Part of going down that road was the form may not be open at the time of running the query, however if I can generate a variable to hold that data for later insertion into the query, that might be better. So it looking at how - something to replace that works - Orderdate:=OD$ in the query grid. Thanks again.

 

[ZOR]

Thought I would post SQL in query.

SELECT Orders.Location, Orders.PWRD, Orders.OrderNumber, Orders.OrderDate, Orders.Description, Orders.FujiPartNumber, Orders.Duration, Orders.UnitPrice, Orders.Qty, CCur(Orders.UnitPrice*[Qty]/100)*100 AS Subtotals, Orders.RunningTotal
FROM Orders;

Orders.Location, Orders.PWRD, Orders.OrderNumber,Orders.Orderdate would be good if they came from public variables.

My orders runningTotal is also becomming a headach as to how its done.

Best regards

 

[Remou]

Here is part one of a suggestion.
[tt]SELECT [Forms]![frmForm]![txtLocation] AS Location, [Forms]![frmForm]![txtPWRD] AS PWRD, Orders.OrderNumber, [Forms]![frmForm]![txtOrderDate] AS OrderDate, Orders.Description, Orders.FujiPartNumber, Orders.Duration, Orders.UnitPrice, Orders.Qty, CCur(Orders.UnitPrice*[Qty]/100)*100 AS Subtotals, (Select Sum([UnitPrice]*[qty]) From Orders A Where A.OrderNumber<=Orders.OrderNumber) AS RunningTotal
FROM Orders;[/tt]

You can see that the running sum is based on the Order Number, which is why OrderNumber is not included as a form control. However, the above SQL may give you some ideas, hence 'part one'.

 

[ZOR]

Many thanks Remou. I think I need to find how to put the order number into the record at the time the quantity is put in, or take out if taken out. Problem being the form is a datasheet/continuos. Had it been a static one record form it wouldbe much easier. Anyway seeing the light at the end of the tunnel on this part helps fathom things out.
Be back. Thanks

 

[Remou]

Another quick thought: how about rattling through the continuous form and adding the records to a table (if that is what you want to do) using a DAO recordset?
I am having a little difficuly visualising your set-up, hence the vague suggestions.

 

[ZOR]

Thanks again. I will try and explain a bit more. I have a mainform, on which another form is placed, being bound to an underlying table which is basically there as a template table made up of 40 product part numbers etc, which come through the form as just label fillers. Also in the table are fields holding unit prices, again as label fillers. In addition there is a field for every item (record) thats accepts a quantity value.
Not all quantity values may receive a quantity input, as this basically is an order form.
When user has finished, all records containing a quantity in their fields get moved into another table which will be held as an order archive, and will get a report made to order the items. When the form gets run again, the underlying table is deleted and replaced with another one ready for the process to run again. Hope thats made it a bit clearer, basically is an ordering system.
As the underlying table is to be dumped, I have now made a query to put the order number in all records, as its only the ones with quantity values in that get kept. Probably an easier way to do this, but it kind of grew in this direction. Back soon. Thanks

 

[ZOR]

Its starting to fly!!. Only problems are the datefield gets corrupted, or its getting a mismatch over its format, however it displays all records in a new table complete with a running total. If I were to remove matching the date, and going for only records with a quantity value, then that should work or am I wrong. Anyway very pleased so far. Have a star, best regards.

 

[ZOR]

The date problem is a format one. I used the expression =Now() in the OrderDate textbox, and when I look at the resulting table it shows Binary for the three query parts related to form textboxes. If I remove the formatting of the date, and type in 16/07/2006 its then readable in the final table. I removed the comparision to OrderNumber and added a >0 to the quantity field so only those records go in. Just need to sort date bit and then its on to reports, what fun!! Regards

 

[Remou]

Yes, it is fun.

 

[ZOR]

Reference only - how to put textbox into query:
SELECT tblTable.Field1, [forms]![frmForm]![txtText] AS TheDate INTO tblMakeTable
FROM tblTable;


SQL I am using. Problem 1, the final table produces binary fields for the three external (on form) textboxes. Might not be a problem.

Main problem 2, the OrderDate will only go into the table if it has been typed in, and not setting its source to =Date(), or onload NameofField=Date().

Code being used:
SELECT data.ID, Forms!OrderMainForm!LCN AS Location, Forms!OrderMainForm!ODate AS OrderDate, Forms!OrderMainForm!ORDNUM AS OrderNumber, data.Description, data.FujiPartNumber, data.Duration, data.UnitPrice, data.Qty, CCur(data.UnitPrice*[Qty]/100)*100 AS Subtotals, (Select Sum([UnitPrice]*[qty]) From data ) AS RunningTotal INTO TESTIT
FROM data
WHERE (((data.Qty)>0))
ORDER BY data.ID;

Any ideas, thanks.

 

[ZOR]

Sorted it out. The query when ran created a table from the maintable. The created tables fields for the three form input boxes kept ending up as binary. I have changed the query to appending and all works okay!! Thanks again Remou if your out there.

 

[Remou]

Glad it is working. I was just about to suggest formatting:

[tt]SELECT data.ID, CInt([Forms]![OrderMainForm]![LCN]) AS Location, CDate([Forms]![OrderMainForm]![ODate]) AS OrderDate, CInt([Forms]![OrderMainForm]![ORDNUM]) AS OrderNumber, data.Description, data.FujiPartNumber, data.Duration, data.UnitPrice, data.Qty, CCur(data.UnitPrice*[Qty]/100)*100 AS Subtotals, (Select Sum([UnitPrice]*[qty]) From data ) AS RunningTotal INTO TESTIT
FROM data
WHERE (((data.Qty)>0))
ORDER BY data.ID;[/tt]

 

[BSman]

When I need to use a variable in multiple places in an application, I normally set up a module to define the variable as a public variable:

public dteThisDate as Date

This works great when you are writing VBA code in forms and reports and need the current value of the variable (or have to set/change the value of the variable).

In order to use the variable in a query, at least when using the query builder, you also need to define a simple function in your module that will return the value of the variable:

Function ValuedteThisDate() as Date
ValuedteThisDate = dteThisDate
end Function

These public variables are also useful for passing application control information, such as when you might get to a particular form from more than one form and you want to return to the correct form. I set up a public string variable called strWhereFrom and use it to decide where to go next from a form when a decision is necessary.

Bob

 

[ZOR]

Many thanks Bob. I do tend to use public variables. I see where you are comming from, so if dteThisDate()as Date is written in a querygrid, then it will go off and find it in a function. Is that correct?, thanks

 

[PHV]

I do tend to use public variables
WARNING: be sure to NEVER have unhandled error in your code as clicking the End button unset ALL your public values ...

 

[BSman]

ZDR,

Yes, that's correct. The function is the only way to get the value of the variable when you set up the query. If you write an SQL statement in VBA code you also need to use the function unless you are setting it up using the current value of the public variable. In that case you would just refer to the variable itself.

Bob

 

[ZOR]

Many thanks Bob, PHV. Have a star for useful bits. Regards.