Code:
Set dbSample = CurrentDb()
Set qdfSample = dbSample.QueryDefs("sqlEnoughStock")
'the below two lines add the query
315hughes
Last Activity: Mar 30 '15, 03:59 PM
Joined: Nov 7 '11
Location:
-
Hi Seth, thanks for your help with this but i managed to find some code that can solve this issue without typing the full SQL manually. below is the code i have used. It is not the code for this scenario i was asking for but its the same pricipal just to help anyone in the future if they get this issue.
... -
thinking about it all i need is somthing like a wait(stop vba) until a button is clicked then carry on vba. As i said i can do this in a input box but not built into a form
Kind regardsLeave a comment:
-
Thats for the responce Nic that is a very good idea. Put i might be narrow minded whn thinking about how to accomplish this. I will still have to start a recordset to get the details the belong to a order. These details need to go into a loop iterating through each part of the order until it is completed. But i would still need to have a loop in a loop where the interior second loop would need user input (enter location and click ok) and this is...Leave a comment:
-
thanks for the responce nic. I would'nt want to offer the users a selection of locations to choose from as this is where stock can get out of date. Its a very linear approach im taking, im trying to remove all options from the end user. although to begin with ill have alot of work getting the database organised hopefully after a month everything sould be in the correct locations.
in your recomendation if i where to have a button next to the...Leave a comment:
-
User entry loop VBA
Morning all. Hopefully i can explain this issue clearly. In brief i have loop 1 with loop 2 occuring within it. Loop 2 has to continue until the correct value is input but if a incorrect value is input i want the vba to stop let the user enter a new value click ok to check and then continue to complete the rest of loop 1 if the value is correct. I can get this to kinda work by using the following vba for loop 2
...Code:Do strLoc = InputBox("Please -
Evening Seth, thanks for that it now works. It must be because i was calling a Query that was created using Query designer. I wonder why this is??
Anyway thanks for all your help. And zmbd thanks for the response aswell.Leave a comment:
-
Afternonn Seth, thanks for the reply. Yes the page is open as the value is entered onto the page for which this vba runs on. Just abit more info if i enter into text14 the value 1 and run the query it comes back with the same results as when the value is set directly to 1 in the sql query.
So could it possiably be that Access cant use a SQL Query that requries values from a user form as a recordeset?? Also just to provide more info the SQL...Leave a comment:
-
Recordset error 3061
Good evening all
I have a recordset that is created from a sql query the sql query is as follows
When i run the SQL i get the correct results but when i run my vba to use this as a recordset it comes up with the error 3061 too few parameters, expected 1. So i change the where clause in the SQL to be...Code:SELECT Item.OrderID, Item.ProdID, Item.Quantity FROM Item WHERE (((Item.OrderID)=[Forms]![frmPickOrder]![Text14]));
-
Recordset comparison
Evening all i am importing a text file into my database the text file is just a number and each new number is on a new line. The method i am using to get the values from the text file is...Code:Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(Application.CurrentProject.Path & "\Import.txt", 1) Do Until objFile.AtEndOfStream l = objFile.ReadLine -
Thanks for your reply's. The aim is to ensure that the content(data) in the XML file. Matches what the xsd/schema says it should be. So a integer is an integer and not a string. This possibly may be a task that occurs when the data is extracted from its origin im not sure. Ill have a think about passing the data from table to table to validate it. Thanks for your responses again.Leave a comment:
-
Validating XML before import
Evening all. I am currently importing XML into a Access 2010 table. It works fine just using a basic
But i am wondering is it possible to have this data validated before it is imported into the tables? Or should this be done be the validation rules when the table designs are created? I have created a XSD file which i understand sets what...Code:Application.ImportXML DataSource:="C:\order.xml", ImportOptions:=acAppendData
-
Vba sql
Hi i am trying to cross reference SQL queries from within VBA. By using the Query Builder i can easily do it and get the correct result but not from within VBA
my table called location is like this
RackID-StockAge-PreAllocate-ProdID
1 01/08/12 true 5
2 02/08/12 false 3
3 01/08/12 false 3
The aim is to get the RackID with the lowest stockage where the ProdID = (var)... -
Hi what i am trying to do is a stock system i use the value from prodnumber to search through a location table to find the oldest stock location number based on that prodnumber value.
The SQL query that does it is the one i posted (also called "strLowestR ack" in the vba code). The value that is returned is held in the value "l"(rack number with the oldest stock) which comes from the Dlookup search. The value "l"...Leave a comment:
-
Thanks for your help Rabbit and TheSmileyCoderLeave a comment:
-
VBA value into SQL query
Afternoon all
is it possiable to put a value from my VBA code into a SQL statement? my VBA code is as follows
...Code:strLowestRack = "SQLtoFindOldestStockfrmAssignOrders" Stop Set rs = Me.[sqlOrderDetails subform].Form.RecordsetClone Do While Not rs.EOF prodnumber = rs!ProdID q = rs!Quantity For i = 1 To q l = DLookup("RackID", -
Looping through a subform records
Good evening all i am trying to loop through a subform records and i need it to display the selected value from each of the records eg:
record 1 prod# 2
record 2 prod# 5
record 3 prod# 3
so in the above senario it would loop 3 times and each time output 2 then 5 then 3. Currently my code will loop through 3 times but constantly output just the first value(2) attached is the code any help would be much appriceated... -
:D Thank you every so much for the help, and you really souldnt appologise your helping me out. That worked a treat you deserve a well earned rest. Thanks again
RLeave a comment:
-
i sure wish i was where ever you where as always wet and windy in the wonderful UK. i copied and pasted your code and i get the error compile error, Expected, end of statement by the , after the L
the code is as follows
...Code:sSQL = "UPDATE [TableToUpdate]SET [ProdID]=" & L , [TimeStamp]= "#" & Format(Date, "yyyy-mm-dd") & "#" WHERE [ProdID] = 0 AND [ID]=(SELECT MIN(ID) AS low FROM [TableToUpdate]
Leave a comment:
-
ZMBD I must say a very big thank you for all of your help. I never did post the new post as it was a school boy error by myself by using the wrong values in the SQL statement. The last comment is very detailed and very informative thanks. But i do have an issue how to i update two values in a SQL update the code is below
...Code:DoCmd.RunSQL "UPDATE [TableToUpdate]SET [ProdID]=800 , [TimeStamp]=#" & Format(Date, "yyyy-mm-dd")
Leave a comment:
-
I presume the " should go at the end of the SQL update statement? and not likebecause that would mean the x is not part of the SQL. Also even after removing the quotes it still wont work i have tried it both ways if i do it this wayCode:Location.ID = " & x
i get a error saying "syntax error (missing operator) in query expression 'Location.ID = &...Code:db.Execute "UPDATE Location SET Location.ID = 0 WHERE Location.ID = & x"
Leave a comment:
No activity results to display
Leave a comment: