Dear All, I have a report with a listbox of 5 coloumns, this report is loaded with a lot of records (600 rec). when the report is loads (in print priview) records beyond the length of the listbox are not shown. how to make the report pages increase (multipage) and the listbox espand automaticaly as much as the number of records ?? Any Report control i can use so i can show all records returned from the query ?? Thanks

0 will always beats the 1.

modified on Wednesday, February 23, 2011 1:23 AM

Dear All I have a form "report_test", and a single button "rptstart", when i click that button, i wrote a code, to start a report in printing preview with some viewing some records. When i click print (printer icon) on the left top corner, the printer prints the form "report_test" with the button but not the actual report am viewing :confused: any body can can guide how to avoid this and print the report itself ??? regards,

0 will always beats the 1.

Dear All, Usage: access 2007, VBA I have 2 comboboxes, cbo1 and cbo2 , cbo1 is loaded with numbers from the database, and cbo2 is loaded with text from the database. dim cb1 as double dim cb2 as string cb1 = CDbl(me.cbo1.value) ' i chossed: 800524 cb2 = CStr (me.cbo2.value) ' i choosed: Hello World msgbox cb1 msgbox cb2 Result : 800524 Result : 4 where 4 represent the location of the "Hello world" , fourth element in the cb2, while i need to get the text :( I can get wat i need from this result (using dataset , and query) but it cost code lines and memory resource, shouldent .Value returns the exact value that is selected ???

0 will always beats the 1.

Thanks again Mika I usually use movelast then move first after my recordset query ... seems i missed this time :sigh: , i used it and the recordcount brings true value (expected number of records). Thanks agian :)

0 will always beats the 1.

The variables scope and eid are in the code are the same as the one run in the access database. I did one step, that i added one more record to my tables that meets the conditions in the sql statement and run both on database and in the recordset, the sql returns true result (expected i.e: 3 records) and the recordset returns only 1 single record. seems that number of records in the record set is stuck to 1 single record. :confused: dont know why And there is nothing special about this record, i mean its not the first record in the tables. its really weird :doh:

0 will always beats the 1.

Dear All, Am running an sql query in Access db , 2007 , and it returns the expected reocrds. Now i copied the same query into a dataset , but it is generating diff numnber of records. Any body can guide me where should i look to solve this. Am writing the query in case it helps :omg: SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.c_scope, tb_c.c_number, tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE (((tb_c.c_scope)="Base Scope") AND ((tb_e_to_c.e_id)=1)); And Here how i wrote in the data set (its runing and returning records, which means the syntes is true, but the numer of records are different): sqls = "SELECT tb_c.c_number, tb_c.c_description, tb_c.c_scope, tb_c.c_category, tb_c.c_type, tb_c.[c_scope], tb_c.[c_number], tb_e_to_c.total_dc, tb_e_to_c.e_id FROM tb_c INNER JOIN tb_e_to_c ON tb_c.c_number = tb_e_to_c.c_number WHERE(((tb_c.[c_scope])= '" & scope & "' AND ((tb_e_to_c.e_id)= " & eid & ")))" set rcs = db.openrecordset (sqls)

0 will always beats the 1.

Wooooohoooooo :cool::cool::thumbsup: Thanks a lot Mika ,i appriciate , it worked perfect , and thanks for the Addidtional note too ;)

0 will always beats the 1.

scope is a string and eid is a int. dim scope as string dim eid as integer scope = CStr(Forms![report_testin]![Combo42].Value) eid =Int(Forms![report_testin]![Combo29].Value) ami defining the string sqls wrong??? I also tried to modify sqls to be: sqls = "SELECT distinct c_number, c_description FROM tb_c INNER JOIN tb_c_details ON tb_c.c_number = tb_c_details.c_number WHERE e_id = " & eid It generates another Error: "The specifies Field 'c_number' could refer to more than one table listed in the FROM clause of your sql statement" but it doesnt make sense for me :wtf:

0 will always beats the 1.

modified on Sunday, February 13, 2011 4:08 AM

Modified to: sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In ( select c_number FROM tb_c_details WHERE e_id = " & eid & " )" Didnt work ... same problem :(( :(( Any different statment that would give me the same results ??? since i think the error is from the IN phrase .

0 will always beats the 1.

modified on Sunday, February 13, 2011 2:51 AM

Dear All, Am getting sytex error , Missing operator on the following: sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In select c_number FROM tb_c_details WHERE e_id = " & eid set rst = db.openrecordset (sqls) Cant find any missing operator :~ , also i used a msgbox to show sqls , the statement is perfect. Helpppp !!! :(

0 will always beats the 1.

Now this does make sense .... nice work around It worked fine (even the result data is not that much comprehensive) but it do the job, and save some extra lines of code ... great man, thanks alot :thumbsup:

0 will always beats the 1.

Thanks Andersson , but i think UNION is not the right solution here, since it envolve a lot of constraints :( , like number of coloumns of the two SQL stat. should be the same. Other wise i think it save alot of lines of code :)

0 will always beats the 1.

Thaks eddy, i found that recordsets are read only by default ... and also are not modifiable if sql depends on two tables of one-to-many relation or invlove sumation of rows ... Also, if above conditions are not true, then a recordset to be modifiable, i think attribs should be set like: set st = db.openrecordset("SQL",2,0,3) or set st = db.openrecordset("SQL",adOpenDynamic,adUseNone, adLockOptimistic)

0 will always beats the 1.

Sure.... The delete statement is just to empty the record set, Why is that? it is because it came from an sql statement or a certain data source, and since i need an empty one , so am deleting the records in it. My purpose is to pass a recordset to a report, but this recordset is a collection or records from different sources ,lets say two coloumns form an sql statement and another coloumn which i need to append and populate. which is in my case the salary coloumn . .append , didnt work . so my guess was to start from with a recordset from any sql source, and delete its rows (initializing) , then add my data. but deleting rows end up in deleting rows from my actual table the recordset came from :~ so i need to start with an empty recordset in order to fill it up and pass it to report.

0 will always beats the 1.

modified on Tuesday, February 8, 2011 10:56 PM

i will not movenext after deletion, but i will add new record.

Mycroft Holmes wrote:

select fname, lname from employee where 1=1

is returning all records in the table.

0 will always beats the 1.

modified on Tuesday, February 8, 2011 5:54 AM

Dear all, I have the following: set rs = db.openrecordset ("select fname, lname from employee") while not rs.EOF with rs .delete end with rs.movenext loop When the code is run, data is directly deleted from my actuall table??? i dont want this to happen, i want to initialize this recordset and refill it with another data. it is deleting the table records directly even without updating (.Update) !!! I need an empty recordset to work on. The only way (i know) to create a recordset is to create one from a given table through an SQL stat. I couldnt find a way to create an empty recordset from scratsh or a dummy recordset without a predefined data source. Help !!!!

0 will always beats the 1.

i just put the sqlstat in a seperate string and it worked fine ;) hello all, when i run this sql stat, in query design, Access 2007 , it works fine and return records. SELECT fname, lname FROM names WHERE (location = 'there' AND id In (select id FROM name_street WHERE street = 11)) But when i put it in recordset as set rcset = db.openrecorset("SELECT fname, lname FROM names WHERE (location = 'there' AND id In (select id FROM name_street WHERE street = 11))") the record set brings back no records ... WHYYY ???? :confused:

0 will always beats the 1.

modified on Tuesday, February 8, 2011 3:08 AM

Greaaaat, thanks alot, this worked excellent but hence i got another problem: when now i want to fill this feild (after creation), am doing: With rs .AddNew .Fields("salary") = x .Update End With where x is an integer from some where, i have the following error: Field cannot be updated . i also used : rs.Edit rs("salary").Value = x rs.Update also same error Any hint why this error showing up ??? is it the right way to fill in this field ?? i think it is :confused:

0 will always beats the 1.

modified on Monday, February 7, 2011 11:36 PM

Dear ALl, i have a record set lets say: set rs = db.openrecordset ("SELECT Name, Location FROM tb1") I need to add a new field "salary" to rs, so i used : rs.fields.append "salary" , adinteger salary is of integer datatype This is not working, it is giving me Typemismatch Error and it highlights **"salary"**, i also googled, all says the same method to append, but still not working ..... AM not sure whats the error, or why , Any one can guide how to append this coloumn to the recordset ??? any work around ??? :confused: Note: I want to fill the values of this "salary" field with numbers from calculations. Regards,

0 will always beats the 1.

modified on Monday, February 7, 2011 5:48 AM

Thanks for the support PIEBALDconsult...:thumbsup: But am now in middle of this application (Access DB, VBA, forms) am not sure that frezzing it at this stage and jump to starting it again (writting the interface) using C# . time is a bit tight... :( may be when i finish it, ill re-do it again in C# so i can build some skills using C#.

0 will always beats the 1.