Search results

Further to Minty's post in which they suggested a simple model in which the many to many relationship is modelled by a third table which resolves the relationship into two one to many relationships, I would add one thing, viz that in each relationship referential integrity must be enforced. An...

Rather than setting the Value property of each control, which is the default property, so is usually omitted, I would suggest that you set its DefaultValue property, and that you do this conditionally in the form's Current event procedure, which will be executed however the form is opened. The...

The point is not the use of the Now() function or a custom function. The key thing is when the Now() function is called. It can certainly be called as the column's DefaultValue property in the table definition, to cater for a row being inserted other than manually in a bound form, but it...

Me too, but with one caveat. If this is done by calling the Now() function as the DefaultValue property, and the row is manually inserted in a form, the date/time value will be that at which the user moves the form to an empty new record. This could significantly differ from the time the row...

You'd need a query along these lines to return the number of distinct donors per accounting year: SELECT AccountingYear, COUNT(*) AS DonorCount FROM ( SELECT DISTINCT AcctYear (4, 5, DTE) AS AccountingYear, Constit_id FROM [PY...

If you want to store the sequential numbers the attached CustomNumber demo illustrates a number of methods for doing so, incorporating error handling to cater for two or more users' inserting new rows simultaneously in a multi-user environment. The method for numbering rows per group would be...

I generally return the complete address in a single growable/shrinkable text box control in cases like this. Any Null lines are suppressed, so gaps are avoided. I use the following function to return the address. This was published many years ago by Microsoft, though I've amended the code...

Try this: SELECT Contacts.ContactTypeID, Contacts.CategoryTypeID, tblCategoryTypes.[Category Type], Contacts.IAMNo, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.Location, Contacts.City, Contacts.StateOrProvince...

There is nothing to stop an INSERT INTO statement from inserting values from an autonumber column in one table into an autonumber column in another table, provided that the unique index on the column in the destination table is not violated. Autonumber is not a data type per se, the data type...

Declaring the parameters is generally not essential, but I did discuss this with John Viescas many years ago in the context of a post we'd received in the old CompuServe Access forum, in which we were both sysops, where the poster had found that parameters in her local date format were not being...

I would recommend that when including date parameters in a query they be declared as such, e.g. PARAMETERS Forms!frmDateRange!txtStartDate DATETIME, Forms!frmDateRange!txtEndDate DATETIME; SELECT TransactionDate, FirstName, LastName, TransactionAmount FROM Customers INNER JOIN Transactions ON...

Firstly, to assign a value to a bound column in the form's current record you should use the bound control's Value property. As this is the default property you do not need to specify it. Secondly a date literal must be delimited with # characters and be in US or an otherwise internationally...

I think I misunderstood your original post. I assumed that the parent form, by virtue of its name, was for inserting a revenue transaction, and consequently that the subform was for inserting multiple line items per transaction. As far as I can see on reflection, the parent form is merely to...

Your frmCDRevenue form is bound to the tblDemoClients table, and its DataEntry property is set to True (Yes). This property is rather confusingly named as it causes the form to always open at an empty new record. Consequently a new row is inserted into the tblDemoClients table. You need a...

The term Cartesian derives from the name of the French mathematician and philosopher René Descartes. A major step in the development of mathematics was his introduction of a coordinate system for defining points on a Euclidean plane by the intersection of two straight axes. This unified...

Rather than calling the EXISTS predicate and multiple subqueries, which could be tedious if the value list were a long one, the attached little demo file illustrates how rows can be returned in the context of a binary relationship by selecting values in a multi-select list box. In the demo this...

I use the following generic function for names and any other sets of values which require concatenating. It was originally adapted from a CanShrinkLines function published by Microsoft many years ago for returning an address, suppressing Null lines: Public Function ConcatValues(strSeparator...

An easy way, which predates conditional formatting, to colour code rows on the basis of a value in a column is to firstly create a table with, in your case, a Notes column its primary key and a further column with a single plain bitmap image of distinct colours. The choice of colours is...

Why not add a Selected column of Boolean data type to the table, or to a separate table in a one to one relationship. You could then either include a check box bound to the column in the subform, or, if the form's recordset is not updatable, select the rows in a separate unbound dialogue form...

The following is a simple example (untested) which replaces a parameter referencing a txtDateApponted parameter with the value of the parameter: Dim strSQL_Current as String Dim strSQL_Temp As String Dim qdf As DAO.Querydef ' replace queries SQL statement , substituting a...