Search results

  1. B

    VBA Recordset based on query with Tempvars

    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 =...
  2. B

    Use function or TempVar in Query

    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...
  3. B

    Combo box dynamic RowSource based on Form Name and Control Name

    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...
  4. B

    Different ways of connecting Access to Azure SQL Server

    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...
  5. B

    Calculated column in SubQuery returns for all records in main query

    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...
  6. B

    Refreshing form bound to a Sharepoint table

    Hi, I have a continuous form (lets call it frmX) in Access 2019 which has a query recordsource which pulls records from a linked Sharepoint list. The form has a function which opens a 2nd unbound form frmY, from which the user can run a SQL statement which will update one or more existing...
  7. B

    Continuous form bound to a Sharepoint List

    Hi all, We have an Access FE which links to a Sharepoint List containing about 70 fields. Each record in the list is completed in a 2 stage process, by different users. Stage 2 cannot be worked until Stage 1 is completed. A continuous form "frmAllocate" is bound to this list and can update two...
  8. B

    Access over remote network - SQL server or Sharepoint Lists?

    Hi, We have a split MS Access database, with the FE distributed to users local C drives and the BE on a shared folder accessed over a VPN. I know not recommended, but it worked well enough when the number of users was low. The problem is now there are 30+ concurrent users and we are getting...
  9. B

    One to One relationship and referential integrity

    Hi, I have a Acc2010 database with a main table tbl_Case and half a dozen or so other tables each of which has a 1-1 relationship with tbl_Case. Each table has a field called CaseID which is the primary key. (The reason I haven't got this as one huge table is that the number of fields would be...
  10. B

    control margin is shown in a different colour if control has focus when form opens

    Does anyone else have this in Access 2010? Sometimes if I set a text box or combo box control margin (say left or top) to re-position the contents within the control, if the control has the focus when the form opens, the area of the control which is taken up by the margin displays in a...
  11. B

    Database password issues

    I have an Access 2016 database split FE/BE as usual. Running in Access for Office 365. When it was created for the client, I put a password on the BE and the FE. All fine. Now I need to change the password. Changed it for the BE no problem. Opened the FE and re-linked all the tables to the BE...
  12. B

    Running a Ribbon command from a command button using vba

    Hi, I would like to add a command button to a form which exactly mimics a particular ribbon command being clicked. Is there a vba command to run a ribbon command, and how do I find the list of ribbon commands available etc.? Basically I want to exactly replicate what happens when you click the...
  13. B

    Access Update Query using result of a Group By Query

    I have a table tblResponses which records responses received for each project in my database. Multiple responses per project, each with a date. Another table tblActivity stores each activity on a project. Multiple activities per project. I want to update each record in the Activity table with...
  14. B

    cn.OpenSchema SUSPECT_STATE

    Hi use the following to return a recordset of users connected to an Access 2010 database. All works fine. My question is: it returns a field called SUSPECT_STATE which I ignore. Can anyone tell me what this field represents and in what conditions it is set? Many thanks, Jim Here's the code...
  15. B

    Updateable query conundrum

    Hi, I have an update query which is not updateable for reasons I cannot fathom. I have two tables, tbl_Response and tbl_Activity. BOTH have a CaseID field which is indexed Duplicates OK (each case can have several responses and several activity records). I want to update a date field in the...
  16. B

    Link to specific object in QuickAccess toolbar

    Hi, Is there any way to add a command to the QAT to open a specific form or table within your database? (Another useful feature which used to be easy in older versions of Access which some brilliant mind at Microsoft decided to remove!) Cheers, Jim
  17. B

    Passing values of variables to custom error handler

    Hi, I have read a very good article on error handling from FMS here. However I would like to know if it is possible to pass and store the values of any variables in use at the point where the code crashed (i.e. similar to looking at the Locals window.) Surely this would be extremely helpful in...
  18. B

    Performance and Best Practice - form recordsource

    Hi, I have taken over a database which is running far too many concurrent users (60+) on an extremely slow network (over wifi! I know!) One of the main forms has a query as its recordset which accesses a table with over 100 fields and 10k records. The query returns all records although the...
  19. B

    VBA Reference to field value in form recordset

    Hi, I have a bound form which has a number of fields in it's recordset which are NOT bound to any controls on the form. In VBA, I need to get the value of one of these fields in the current record. However the field I need will change each time the procedure is run and is determined by the vba...
  20. B

    Single Index or Multiple Column Index

    Hi, I am creating a table with a single memo field and either 3 text fields (tablename, fieldname, caseID) or a single text field with the 3 text strings concatenated. There will be a single NO DUPLICATES index on either the 3 fields or the single concatenated field. My question is this: from...
Top Bottom