Search results

As it stands, with my amendments, your form only allows prices to be increased. The form below is from one of my demo files and allows proces to be either increased or decreased by means of an option group. The options values are 1 for increasing prices, and -1 for decreasing them. The code...

I think the following is what you need: 1. Format the txtPrices control as Percent. 2. Change the CheckStatus control's Click event procedure to: Private Sub CheckStatus_Click() Dim strSQL As String 'make sure any unsaved changes are saved to avoid conflicts: DoCmd.RunCommand...

I agree that a JOIN should be the first choice. I was really comparing the (NOT) EXISTS and (NOT) IN predicates.

You could change the RowSource of the CandidateID combo box in the subform to: SELECT VacancyCandidate_Tbl.CandidateID, [CandidateLastName] & ", " & [CandidateFirstName] AS Candidate FROM Candidate_Tbl INNER JOIN ( Decision_Tbl INNER JOIN VacancyCandidate_Tbl ON...

The NOT EXISTS predicate is very efficient provided that SELECT * is used in the subquery as this allows optimal use of the indexes. Mathematically the underlying difference between the EXISTS and IN predicates is that the former is an operation of the relational calculus, whereas the latter is...

It occurs to me that you might be tempted to enforce this by applying a CHECK CONSTRAINT to the table. However, that would preclude the possibility of a candidate being appointed to one position, and, at some future date being promoted to another position. When recording a candidate as...

Now that you've discovered the NOT IN operator one thing you need to be aware of is that, should the column in question in the subquery be NULL in any row, the query will fail. You can see why if you expand it algebraically: a NOT IN (x,y,NULL,z) expands to: a <>z AND a <> y AND a <> NULL...

With the amendments to VacancyCandidate_Tbl which I described, the candidate's success or rejection for the vacancy in question would be recorded in the Decision column. If they are under consideration for more than one vacancy this would be represented by multiple rows in VacancyCandidate_Tbl...

Objects can become corrupted. If you ever have the same sort of problem again try renaming the secondary form and then copy it to the clipboard. Paste it back in and save it with the name of the original form. Then see if it works as expected. I've found that in the vast majority of cases...

The attached little demo file illustrates the use of functions which identify whether the values of data have actually been changed in a row, rather than merely that the row has been updated. The AfterUpdate event procedure can be executed if no actual changes have been made, e.g. if a user...

I would base the query on a second query which draws upon your original query, and references an unbound combo box and two unbound text boxes as parameters in the header of a form in which the values are entered. This second query should be the RecordSource of the form: SELECT * FROM...

If the LinkMasterFields and LinkChildFields properties of the subform control are the names of the primary and foreign keys respectively, that will always be the case. However, you can reference the query directly, and can return the value with an expression as the ControlSource property of an...

But that does not satisfy the OP's stated requirement, viz: 'A user who knows nothing about Access or databases would expect it to work for the whole form just like it works for an entire Excel spreadsheet, so yes that's what I want to find an alternative to'. Their clarification in post #8...

Rather than having separate forms I would suggest the use of two correlated subforms, both in continuous forms view, within an unbound parent form. The first subform is based on the referenced table, the second on a query on the referencing table which references as a parameter a control...

What Tom says, 'Job security: if the people upstairs ever define a new exam period, I will need to update and test all related queries, and who knows what else...' underlines the importance of following Codd's Rule #1, and always storing data as values at column positions in rows in tables. As...

Almost invariably I would use controls in a form, which could be a bound form or an unbound dialogue form, as each parameter. You can either enter the values into the controls manually or assign them with code. The important thing is that the form is open and the values have been inserted into...

If only!

RonPaii's solution restricting the outer query by means of a subquery is much better than simple joins. However, it does not return the Disciplines available from each contractor. This can be overcome by calling a concatenation function. The following is an example of mine which uses the...

I would rename plog's VacancyCandidate_Date column as ApplicationDate, and add a DerminationDate column to VacancyCandidate_Tbl. You'll then have a record of when each candidate applied for each vacancy, and when their application was determined. Rather than a Boolean Success column I'd have a...

The attached little demo file uses a multi-select list box in the way described by MajP to select multiple parameters. A report is then opened, with the choice of returning all rows which match all or any of the parameters. The methodology used is similar to tMajP's query, but in my case...