Recent content by Ken Sheridan

An easy way to achieve such a layout is to use a report which returns distinct order date and number values, and place a subreport alongside the rightmost control in the detail section. The subreport should be in multi-column format with across-then-down column flow. If there are insufficient...

The following uses the highly efficient GetString method of the ADO Recordset object: Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String Const NOCURRENTRECORD = 3021 Dim rst As...

That had occurred to me. The following should handle it: SELECT S1.Customer_ID, S1.Order_Date, S1.Order_No, S1.Item_Code, MIN(S1.Pick_Bin) AS Pick_Bin_1, IIF( ( SELECT COUNT(*) FROM Sales AS S2...

The following is code behind a button in a dialogue form in which multiple addresses can be selected in a Multi-select list box. Private Sub cmdConfirm_Click() Const conSQL = "UPDATE SelectedAddresses SET Selected = FALSE" Dim varItem As Variant Dim strSQL As String Dim...

In the 28 years in which I have been answering newsgroup and forum posts I have always based my replies on the same basis as I would in my professional life, that any conclusion can not be more than a working hypothesis. If this can be tested by observation then the hypothesis stands until...

The attached zip archive includes a number of files of varying degrees of complexity which illustrate how image files can be associated with records in a database, using the methods described in this thread. The basic Images_Simple.accdb file should suit your requirements.

The following is an example of code which requeries a form after a row is updated. As the Requery method reloads the form's recordset, the code then navigates back to the newly updated row. Private Sub Form_AfterUpdate() Dim lngID As Long ' assign current row's primary key value...

You might find the following query which records the monthly payment of membership fees of interest: SELECT T1.TransactionID, T1.MemberID, Members.FirstName & " " & Members.LastName AS FullName, T1.TransactionDate, T1.FeeDue, SUM(T2.FeeDue - T2.FeePaid) - (T1.Feedue -...

You miss the point. When the form is filtered so that no rows are returned all fields will be Null in the empty form provided it has no DefaultValue. Therefore if a field has been constrained as NOT NULL, i.e. its Required property is True (Yes), the field's being Null will mean no row has...

Rather than examining the form's Recordset property you could examine any NOT NULL column for NULL: Dim yearVal AS Variant yearVal = Me.cboYear If IsNull(yearVal) Then MeFilterOn = False Else Me.Filter = "Year(DateOfReceiving) = " & yearVal Me.FilterOn =...

I'm late jumping into this, but can't you use a simple aggregating query: SELECT Customer_ID, Order_Date, Order_No, Item_Code, Min(Pick_Bin) AS Pick_Bin_1, Max(Pick_Bin) AS Pick_Bin_2 FROM Sales GROUP BY Customer_ID, Order_Date, Order_No, Item_Code;

You can sequentially number each row returned in a report without any code. Set the ControlSource property of an unbound text box to =1, and set the control's RunningSum property to Over All.

I have an orders form whose RecordSource is as follows to restrict the form to orders not yet invoiced: SELECT * FROM Orders WHERE Invoiced = FALSE ORDER BY OrderDate; In the form a button opens an invoices report at the current order with: Private Sub...

If, when each new instance of a form is established, it is assigned to a collection, a specific instance of the form can be referenced by its ordinal position in the collection. Focus can then be set to that instance and the Close method of the DoCmd object called without any further arguments...

I share LarryE's concerns about your table design. It should be possible to return different counts of entities where each count is determined by some attribute of each row in the source table. The following is an example of a simple query which returns the total number of transactions and the...