Solved Place value in field in tbl1 and put it in field in tbl2 using a form

[regnewby2465]

I am designing a db to be used for dispatch purposes. There is a parent form CAD_DallDispSplitF linked to its subform CAD_Log_DispF via ID_Activity. Works fine.

The subform CAD_Log_DispF, shown below, gets it data from CADLogT (linked to Sharepoint).

I have table name LocalUserT which has the following fields:

I need a way to grab the EmployeeID from LocalUserT and place it in the Employee field of the subform CAD_Log_DispF.

I created update query qryUpdateEmployeeinCADlog as shown below. I did not link them because when I do, I get no matching records. The query updates correctly when I run it manually.

I placed the following code in the subform's After Update event.

Private Sub Form_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateEmployeeInCADlog"
DoCmd.SetWarnings True
End Sub

When it fires it does not update Employee field of the current record. When I enter a 2nd record in the subform, the update query fires, and will update the previous record's Employee field but not the current record, etc. I have limited VBA skills but I am learning. Thanks. Mark

 

[The_Doc_Man]

OK, first things first: WHEN do want that to happen? I'm asking because Access is based on triggering events. That is, something happens to "wake up" Access to do something. We call these things "events" and they are the place to put code. So what event, what action, what user interaction ... will set up the condition for this transfer?

It also matters as to where you have control at the moment, because events happen in the context of the thing that is in control. If the control is in the sub-form, that is one approach. If the control is in the main form, that is another approach.

Once you have the WHERE and WHEN figured out, the WHAT will be easy.

 

[regnewby2465]

I hope I understand your question. The LocalUserT.EmployeeID is already set. It is left alone.

I have just entered a record in the subform CAD_Log_DispF by tabbing all through the controls. My thought was that after I exited out of that record by tabbing, that it would invoke the

Private Sub Form_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateEmployeeInCADlog"
DoCmd.SetWarnings True
End Sub

The curser then goes to the first control on the new record, as it should. It does fire correctly but as I stated before, it does not update the Employee control in the record I just entered.

 

[The_Doc_Man]

Don't turn off warnings while developing. They will provide valuable information, and once you get everything working, you won't NEED to turn them off again. With warnings off, you have no way of knowing whether Access would tell you "1 record updated" or an error message. But since you didn't update anything, I think the odds are you missed getting an error message.

EDIT: After re-reading post #3, tabbing through a record doesn't do anything unless there is some default value being applied. If nothing changes, there would BE no update.

 

[gemma-the-husky]

@regnewby2465

if you tabbed through a record until you got to a new record, did it create an empty record in your table. If not maybe your form cycle property is set to single record, rather than all records, and you are still on the same (un-edited) record.

if you do have a blank record, then you might have got a afterinsert event, rather than an afterupdate, as @The_Doc_Man said. Maybe you end up with a blank record other than an autonumber PK, something like that.

You could add msgboxes in the events to trace the process. Also as @The_Doc_Man mentioned, you need to understand the events that happen. Sometimes it's not quite what we think. If you don't add an afterinsert event handler, the event will still happen, it's just at access won't react to it. A lot of development is making absolutely sure we know what access is actually doing.

Also as @The_Doc_Man said, the insert query may have failed, but you didn't see the reason why because setwarnings was false. if it's true, you might get a "not all your updates were processed", and the numbers show that in fact zero updates were processed. 0

instead of opening the update query you can do this, which might be more helpful. Note that openquery, and currentdb.execute are similar but not identical because of the way they respond to errors. They both have uses.

on error goto fail
currentdb.execute "qryUpdateEmployeeInCADlog", dbfailonerror
exit sub

fail:
msgbox "Error: & err & "  Desc: " & err.description

 

[regnewby2465]

gemma-the-husky​

Thank you for the response. When I tab all the way through my form and land on the first field in a new record, the record that I just left has all the data I entered minus the value that I needed in the Employee field from the LocalUserT.EmployeeID.

 

[regnewby2465]

Don't turn off warnings while developing. They will provide valuable information, and once you get everything working, you won't NEED to turn them off again. With warnings off, you have no way of knowing whether Access would tell you "1 record updated" or an error message. But since you didn't update anything, I think the odds are you missed getting an error message.

EDIT: After re-reading post #3, tabbing through a record doesn't do anything unless there is some default value being applied. If nothing changes, there would BE no update.

Tabbing through all the fields to the first field in a new record, I would think it would fire the After Update event since I did change the fields from nothing to having data in them. Am I thinking wrong. Thanks.

Mark

 

[Pat Hartman]

In the BeforeUpdate event of the subform, populate the field:

Me.EmployeeID = DLookup("EmployeeID", "LocalUserT")

Never use a query to update the form's bound record.

I'm assuming that the LocalUserT has only a single record and that is why your Cross Join is working. With only a single record in LocalUserT, the Cartesian Product won't "duplicate" data.

PS, you don't want the update to fire just because the user tabbed through fields in a record. It only makes sense to update the field if the user updated some field of the record. UNLESS, you are trying to log the ID of the user who last viewed the record whether he updated it or not.

If you want to log viewing a record, you need to put the line of code I wrote above into the subform's Current event.

PPS --- NEVER, EVER update a record using the Form's AfterUpdate event. This puts the form into a tight loop. It is only because you are using a newer version of Access that you will survive. Older versions just froze up and the screen went to a violent flicker. You had to reboot to stop the loop. These days, Access is smart enough to recognize the recursion in the call stack and exit gracefully after some number of iterations of the loop. The AfterUpdate events runs exactly when its name implies _ AFTER the record has been updated. So updating the record in that event forces access to mark the record as dirty and therefore it needs to be saved. To do that Access runs the BeforeUpdate event, saves the record, then runs the AfterUpdate event and your code dirties the form sending it back to the on dirty, beforeUpdate, afterUpdate and over and over and over again until Access recognizes the loop and stops it.

 

[The_Doc_Man]

Tabbing through all the fields to the first field in a new record, I would think it would fire the After Update event since I did change the fields from nothing to having data in them. Am I thinking wrong. Thanks.

Mark

Tabbing through a field does NOTHING unless there is a default value involved somewhere. Typing something AND THEN tabbing out updates the field (and thus, the record.) Tabbing through a virgin record without changing anything will NOT fire an AFTERUPDATE or an AFTERINSERT. It will fire nothing except maybe some kind of error.

 

[gemma-the-husky]

^^^
hence you wouldn't have thought a blank record would be created. If a blank record does get created, then some event must fire.

 

[Pat Hartman]

That's what proper validation prevents

 

[regnewby2465]

In the BeforeUpdate event of the subform, populate the field:

Me.EmployeeID = DLookup("EmployeeID", "LocalUserT")

Never use a query to update the form's bound record.

I'm assuming that the LocalUserT has only a single record and that is why your Cross Join is working. With only a single record in LocalUserT, the Cartesian Product won't "duplicate" data.

PS, you don't want the update to fire just because the user tabbed through fields in a record. It only makes sense to update the field if the user updated some field of the record. UNLESS, you are trying to log the ID of the user who last viewed the record whether he updated it or not.

If you want to log viewing a record, you need to put the line of code I wrote above into the subform's Current event.

PPS --- NEVER, EVER update a record using the Form's AfterUpdate event. This puts the form into a tight loop. It is only because you are using a newer version of Access that you will survive. Older versions just froze up and the screen went to a violent flicker. You had to reboot to stop the loop. These days, Access is smart enough to recognize the recursion in the call stack and exit gracefully after some number of iterations of the loop. The AfterUpdate events runs exactly when its name implies _ AFTER the record has been updated. So updating the record in that event forces access to mark the record as dirty and therefore it needs to be saved. To do that Access runs the BeforeUpdate event, saves the record, then runs the AfterUpdate event and your code dirties the form sending it back to the on dirty, beforeUpdate, afterUpdate and over and over and over again until Access recognizes the loop and stops it.

Thank you Pat. That seemed to work. I will close this thread out now. Again, thanks.

 

[Pat Hartman]

You're welcome