Search results

Hi Thanks for that. The two errors you pointed out were actually OK. The , "" Bank is a blank column. The gap in the form control I have no idea how that got there as I used 'Build' to input the control. With the help of your code and making sure the original query isn't already 'make...

Hi, I have a querydef with one input parameter. Set myDb = CurrentDb() Set rs = Nothing Set qdf = myDb.QueryDefs("qryAssetDetails_lessBank") qdf.Parameters(0) = Me.cboChannel strName = "tblTempRS" strIn = qdf.SQL strOut = Replace(strIn, "From", "INTO " &...

Hi Bob, Yeah you're right. The outer RS loop does use qdf.parameters(0) to pass a channel name from the form but, when I resuse the query in the inner RS loop I'm not. The inner loop uses a SELECT statement with the query. Is there a way I can pass the criteria into the SELECT statement or...

Hi I've decided to try removing the outer join from the querydef and then using the resulting RS with a nested RS using a SELECT with the dbo_C_Bank table to enable detection of missing dbo_C_Bank records. i.e. I've created a blank column in the original querydef and removed the outer join...

Hi As requested and design jpeg attached SELECT dbo_ASSET.ASSET_ID, dbo_ASSET.ASSET_CODE, dbo_ASSET.RUNNING_DISTANCE, dbo_COMPONENT_TYPE.COMPONENT_TYPE_DESC, dbo_C_BANK.BANK_POSITION, dbo_CONDITION.RATING, dbo_ASSET.OWNERSHIP, dbo_ASSET.NUMBER_ON_PLAN FROM (((dbo_CONDITION INNER JOIN...

Hi The upload didn't work the first time

Hi, I will check for corruption but I don't beleive that is the situation. I've attached a display of the query design and the associated output. Note the Outer Join. A bit of background; Waterways and Banks have either a 'L' or 'R' (Left or Right) You'll notice that the #Deleted appears...

Hi, I have a query with a Left Outer Join. The query returns a #DELETED value in the returned recordset if their is no corresponding record. This is what happens when I run the query stand alone. When I run the query using recordsetopen into an RS and try to read each record I eventually get...

Thanks for the reply. The recordset is fine (58 records) so there's no problem with getting the data its just the array loading. For this task I'll just loop thru the rs to load an array. It'll do the job as the number of columns is static and the number of rows is available via recordcount...

Hi, I'm using the getrows recordset method to load a variant array but the not all records are loaded. The recordcount is correct but the UBound value is way to small. In all the attempts I've tried I have noticed it did work occasionally. It makes me wonder if I have some resource problem...

Hi Thanks for the info I've created a loop in vba for each service_point_desc. I'll then insert the result into a temporay data table. I will then simply AddItem from the the temporay data table into the list box. All good I thank you all Cheers bob

Thanks to both Bob & Namliam. I don't really have access to the SQL Server so I'll try the first of Namliam's options. I'll let it be known how I fair shortly. Cheers Bob

Hi The following SQL results in a quick result; SELECT WRHUB_PROD_V_SRW_MID_PARCEL_SP.SERVICE_POINT_DESC FROM WRHUB_PROD_V_SRW_MID_PARCEL_SP WHERE WRHUB_PROD_V_SRW_MID_PARCEL_SP.SERVICE_POINT_DESC IN ("MC.4033", "MC.678"); However, if I use a subquery as follows; SELECT...

Many thanks, That sounds good to me. Cheers Bob

Hi, I'm creating an ERD. I need to be able to store object attributes where the object types may be many and the associated attribute types may be many or few. I've seen examples where there is an 'Object_Type' table and a 'Attribute_Type' table, but how do I relate these two tables to...

Thanks Psycence The solution works great. Unfortunately it only returns proposal_application records for one cust_id. You weren't to know, so I apologise for that, but it is possible for the one short name to have multiple cust_id's. One of the IT people hit on the reason the original SQL...

Hi, I'm to mess around with the recordsource string. The CLEAR button on the form works perfectly using strFilter = "SELECT * FROM tblProposal_Application" as the input to the recordsource The GO button (Filter) does the same thing if I take out the WHERE clause. When I add the WHERE...

Hi Guys, Apologies, the value I wish to display is the cust_name from the tblCustomer table which is linked to the stored cust_id value in the current record. I've attached a stripped down version of my database. The customer name combobox 'cboCustomer', has two fields, cust_id and cust_name...

Thanks for the reply This is the line that is executed after the recordsource change; Me.[cboCustomer] = Me.[cboCustomer].ItemData(0) I get an error message; 'You can't assign a value to this object' Interestingly I have another command button to 'clear' the filter comboboxes and...

Hi, I'm changing the recordsource of a form using an SQL string to filter the records. The correct number of records appear but a bound combobox doesn't display the value for the first record in the newly filtered set of records. I've tried using me.refresh after the recordsource change...