Recent content by BiigJiim

Thanks for your replies. Does that mean that basically you cannot open a recordset in vba with a query that references a tempvar? (I am sure I have done this lots of times in the past, but maybe I am mistaken.)

Hi, I have a bit of vba which assigns a tempvar and then opens a recordset based on a SELECT query "qry_InsertLetter_C1" which is using that tempvar in the WHERE clause. The query uses linked tables to a SQL Azure database. TempVars.Add "myRPID", 35 Set rsIn =...

Thanks guys. I'm going with a Tempvar - can't see any obvious downside and the persistent value hold on code break is quite useful when developing!

Hi, I have a query which returns a single Case record, based on a case ID number which can be supplied from a number of different places. Is it better to create a global variable and a function which reads it, and then insert the function into my query WHERE clause, or should I be using a...

The idea was that the form would autoupdate itself when it opened. If it was possible in the control properties window to set a property based dynamically on other properties of the same control, this would be easy, but sounds like it is not possible to do that. I think I am just going to have...

I did think about using the Tag property, with a template form that runs code when it opens to loop through all controls and set the Rowsource for the ones with the right Tag - that would be easy enough to do because the code would "know" the name of each control and of the form. But that relies...

Agree it is a very odd request. And I think I am going to have to tell them its not possible. I just wondered if there was a way of referencing the control name (and form name) in a dynamic SQL string or formula in the control properties. I think they will have to accept a standard SQL string...

Thanks arnelgp . This is clever - I never realised you could use a function as the RowSourceType of a combo. However, it still doesn't achieve what the client wants, as if using the same function for every combo box, it would require a way of passing the control name into the function. The...

Each combo will have the same number of columns. The Item will always be the same data type, ie text. So if the Lookup_Table looked like this: FormName ControlName Item frm_X ctl_ABC Item A frm_X ctl_ABC Item B frm_X ctl_ABC Item C frm_Y ctl_DEF Item D frm_Y ctl_DEF Item_E frm_Y...

Hi, I am not sure if this is possible (or even if it is a good idea!) but is it possible to use the form name and control name to dynamically build the RowSource for a combo box? I have a client who wants to be able to copy a combo box from a default template form to new forms in design view...

Thanks guys. Sounds like bound forms and ODBC linked tables is the way to go. I just remember the first SQL/Access application I picked up from a SQL developer was completely unbound forms, connections and not a linked table or pass-through query in sight! But it was a long time ago, maybe...

Thanks Minty. I’m not too worried about write conflicts in this application, as records will be allocated to individual users, so only one use will be able to access a record at any one time. Given the ease of ODBC though, I am wondering why it is ever more appropriate to use unbound forms.

Hi, I am setting up a new application with an Access FE and an Azure SQL Server BE. In the past I have connected Access FEs to SQL Server BEs using either ODBC linked tables with bound forms, or unbound forms with VBA to open connections to populate unbound controls, update records etc...

Hi all, I have a main query which returns every record in tbl_P2_Review which meets certain criteria, plus a number of fields from related tables. Returns approx 4000 records. I want to include a column which flags records in tbl_P2_Review which have a very specific set of criteria. So I...

Thanks for your replies. Requery works fine - I just don't understand why Refresh doesn't (as I am not adding new records.) Especially when the "Refresh All" ribbon command seems to work ok. Is it some limitation of using linked Sharepoint tables?