Converting Access 97 program to Access 2002 (Access 97/2002)

Topic

New Reply

I have several headaches upgrading a perfectly good Access 97 programs to Access XP (2002). Two of the problems involve not being able to make updates to bound text boxes but I am able to update the underlying query. I found a work around for one but I need help with this:

I keep getting the message

Reply | Quote

Replies

The query engine underwent some drastic changes between A97 and A2000 and it sounds like you’re getting bitten by one of them. It isn’t logical to remove a key from the ‘one’ side of a join if that would result in an orphan record in the ‘many’ side. Why don’t you post the code you’re trying to use and maybe someone can spot the problem and help you fix it.

Reply | Quote

I made a mistake in my original post, the error is popping up when I try to make changes in the query. This problem is not based solely in the code.

This database tracks adverse unemployment determinations. The user inputs the claimants address information on the first screen.

The problems are popping up in the determination screen that is next . It uses the query shown below. The attachment is a picture of the query builder. The main table, tblDeterm, is at the leftmost side of the query with various other tables.

The reasons for making a determination are in the tblDetCode. We record the type of determination (the issue), the basis for the determination, evidence collected, action taken and reference the code (subpart of the law) they are violating. Generally they use the default wording on each of the paragraphs, and the tblDeterm table records the determination code from the tblDetCode and cross-references to that table. Sometimes they make changes. When a change is made to a specific paragraph the associated Y/N field is marked yes in tlbDeterm and the new text of the paragraph is recorded in the respective tblPara* table.

The error comes when the user tries to change the determination. Of course any Y/N fields that were marked need to be reset to no and the paragraphs need to be erased so the default information of the new selection shows. The paragraph tables are causing the problems.

Here is the code that runs when the combo box displaying the determinations codes is changed. Those that begin with * are causing the problems.

Private Sub cboDetCode_AfterUpdate()
On Error GoTo Err_cDC_AU

‘change DetID
UpdateDetID

‘reset field on Det and RDet tabs to default
txtIssueInput.Value = DetCode1.Value
txtBasisInput.Value = DetBasis.Value
txtReferInput.Value = ProcRefer.Value
txtActionInput.Value = DAction.Value
txtRDBasisInput = “”
txtRDActionInput = “”
*DateRDet = “”

Reply | Quote

What do you mean, when you try to make changes “in the query”? I *think* you’re talking about making changes through a form based on a query. Is that correct? I don’t quite understand why you’re including the lookup value tables like tblDetCodes in your query at all, since you don’t need them there in a form based on tblDeterm. You only need the linking field from tblDeterm. You would ordinarily bind a combobox on the form to that field and populate it with a list of values from tblDetCodes.

Reply | Quote