Event timing?

[gakiss2]

Private Sub CtlRefNum_BeforeUpdate(Cancel As Integer)
Const MESSAGETEXT = "A record with this value already exists."
If Len(Me!CtlRefNum.Text) > 0 Then
If Not IsNull(DLookup("RefNum", "TblReports", "RefNum = """ & Me!CtlRefNum.Text & """")) Then
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
Cancel = True
Me!CtlRefNum.Undo
DoCmd.FindRecord Me.CtlRefNum.Text
' How to get rid of the empty record left behind after this discovers it is a replacant
' DoCmd.GoToRecord , , acLast
' DoCmd.RunCommand acCmdDeleteRecord
End If
End If


End Sub

The above is code attached to before update on a field where 'RefNum' is entered. For now I am working on behavior if a new record is being entered using FrmReportEntry. If RefNum is a new number nothing happens and that is the desired behavior. If the entry for RefNum already exists then I want to jump to that form so it is added to / changed rather than make a new record. (yes, I tried just setting RefNum to 'no duplicates', it keeps you from saving the record and gives you the warning after you have wasted time entering data).

So from the user perspective, this works beautifully. The database jumps to record with the RefNum that was entered ready for additional information. The problem is that when this happens it leaves behind a near empty record consisting of only the fields the user entered before entering the RefNum which shows up early in the form. It makes a new one each time this code gets activated. Besides the ugliness of useless data, the near empty records show up elsewhere in other forms where they are not wanted. I tried to add code to go the last record and delete this unwated record but if I do that before I DoCmd.FindRecord then the record can't be found. If I try to do it after then the record I wanted to show goes away. I think this has something to do with trying to do the right thing on the right event but not sure. This may be obvious from the code but if take out the DoCmd.FindRecord then the near empty record is not created.

Another related issue is when I attempt to edit an existing record by changing the RefNum to one that already exists. This really shouldn't happen in practice but of course it could happen as an error or stupid user. I'd be OK in never allowing that field to be edited after initially entered but haven't looked for how to do that yet. I only mention it here in case both birds can be killed with one stone.

I have thought about a Delete Query but that would be new territory for me.

Thank You in advance

 

[theDBguy]

Hi,

Me.ControlName.Undo undoes what the user entered in the box, but Me.Undo will undo the entire form, essentially discarding all user input.

Hope it helps...

 

[isladogs]

If RefNum is the primary key field or indexed with no duplicates allowed, Access will do all of this automatically preventing the same value being reused.
You can also opt to lock the form when viewing existing records but you need to consider how to overwrite that if edits are needed.

 

[gakiss2]

Tried Me.Undo

Me!Undo didn't work but Me.Undo has issues

maybe similar to when I tried to delete the record?

Run-time ero '2142'
The FindRecord action requires a find what.

Quick google on error 2142 yielded almost nothing. Just the obvious, the
'find what' is missing. So I'm thinking that while me.undo is on the right path, it is also removing the 'RefNum' entry I am using to FindRecord. Can I put the contents of the control in a variable then use that variable for findwhat?

Dim strRefNum as String
strRefNum = ctlRefNum [this is the part I am not sure about]
...
DoCmd.FindRecord

"strRefNum",, True,, True
​

???​

 

[gakiss2]

isla: Tried that, works to prevent duplicates just terrifically. Doesn't give desired behavior. You get a message that the record won't be saved after you have entered a bunch of data, too late.

Thanks though.

 

[theDBguy]

Hi,

Yes, put the user entered value into a variable first before resetting the form, so you can continue to search for it.

 

[gakiss2]

Private Sub CtlRefNum_BeforeUpdate(Cancel As Integer)
Dim strRefNum As String
strRefNum = Me.CtlRefNum.Text

Const MESSAGETEXT = "A record with this value already exists."

If Len(Me!CtlRefNum.Text) > 0 Then
If Not IsNull(DLookup("RefNum", "TblReports", "RefNum = """ & Me!CtlRefNum.Text & """")) Then
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
Cancel = True
Me.Undo
DoCmd.FindRecord strRefNum
' DoCmd.FindRecord Me.CtlRefNum.Text
' How to get rid of the empty record left behind after this discovers it is a replacant
' DoCmd.GoToRecord , , acLast
' DoCmd.RunCommand acCmdDeleteRecord
End If
End If


End Sub


No errors and the entire form gets emptied (Me.Undo) but now its not doing DoCmd.FindRecord strRefNum.

Something wrong with either

strRefNum = Me.CtlRefNum.Text

or

DoCmd.FindRecord strRefNum

???

 

[theDBguy]

Hi,

What does the variable contain? Maybe try enclosing it in quotes?

.FindRecord “‘“ & var & “‘“

Sent from phone...

 

[gakiss2]

var type is text. Its actually numerals but don't I don't think that matters.

I tried "'" & strRefNum & "'"

No errors but still FindRecord doesn't work. The form just sits there blank like it did before I added FindRecord. Only difference is Me.Undo is emptying the entire record. The behavior is just like Me.ctlRefNum.undo except any fields filled in are erased.

Is there a command that is needed to move it past the Me.Undo so it gets to the line FindRecord? Or is there just something wrong so that FindRecord isn't finding anything???

I appreciate your help. I need to do my regular job for a while. Hoping I can respond to your next advice later this afternoon.

 

[theDBguy]

Hi,

Are we using this code to a bound form? If so, does the record source include all the data in the table? How does the user initiate adding a new record?

 

[gakiss2]

OK

The form is FrmReportEntry the control source is TblReports. The field is RefNum. The control is CtlRefNum. It is a Short Text field. The actual entries are numerals but I think that doesn't matter. I don't use them to make any calculation. The purpose is an identifier for the record. When there is an 'issue' it is assigned one of these. They should be unique.

The control on the form is bound to RefNum on the TblReports table. I believe this means the source includes all the data. I don't know why it wouldn't.

So far I have only been focusing on the behavior of the form when I am in add record mode. I am getting there by using the standard arrow buttons. I did try it once (change the existing RefNum value to a known existing value) in edit mode and it didn't fire. I don't know why it didn't but didn't pursue it since I had planned to simply disable the control in that scenario. I'll have to search around for how and hopefully figure it out myself but if not, I planned to throw it to this crowd.

 

[theDBguy]

Hi,

Can you tell us what is in the following properties of your form? Thanks.

Record Source
Data Entry

 

[gakiss2]

Record Source: TblReports
Data Entry: No

Form is meant to open in a Read Only Mode. I have a button on the form which reopens it in Edit Mode but all that I have been talking about is when first opened as read only then clicking the built in new record button at the bottom of the window.

Maybe Access changes the Data Entry mode when that occurs??

 

[theDBguy]

Hi,

What happens if you simply open the form regularly from the Navigation Pane? Does it still not work?

 

[JHB]

Followed your description, it works exactly as you wanted in the attached database.
So could you post your database with some sample data, (zip it)?

 

[gakiss2]

The db is attached. I pulled out a lot of records to make is smaller but there is some left to see function. I understand you all are not Industrial Spies but please keep the data confidential. My test record has been RefNum 106526. In the attached you will see the 'orphan record' which the working method creates. Been trying other methods to avoid the orphan records but so far no luck. I put lots of comments in the code which is the 'before update on ctlRefNum. this is where the user inputs the Reference Number. The purpose of all of this is so a user enters some of the information into the record when it is initiated then, later, when a report is written up the user can enter the RefNum and be taken to the record for that RefNum at which point the user can attach the report, add more notes, other attachments or etc.

The db opens on FrmTblReports which is a listing of the records. If you click on the RefNum then the FrmReortEntry launches. There you can view the record, open attachments, add notes and etc. Then if you want to add a new record you click the native 'add record button that Access puts automatically at the bottom of the form. The user starts entering data then enters the RefNum. This code looks to see if it is a duplicate. If it is not then nothing happens and the user continues to enter data into the form (it must actually be a 'new' record'. The systems which create these RefNums wont' allow duplicates at least in my years of experience with them). If it is, then the form should jump to that record and the user can add whatever additional information, attachments, notes and etc.

Writing this out gave me a thought. Maybe it would be better to add a button specifically for a New record and another specifically to go to an existing record. That might give us more control over db behavior. However, even so, I would need to catch the operator entering an existing RefNum while in Add Record mode anyway so maybe that takes us right back were we started?

 

[gakiss2]

Reply to DBguy:

open straight from Nav: Opens on the first record in the table in read only mode (by design), can't enter anything into RefNum. there is a button to 'Edit Record' Runs a macro which closes the form and reopens it in Edit Mode.

I do that then enter an existing RefNum (shouldn't be normal user behavior) I get the warning message (record with value exists (as intended) then any changes are undone (Thanks to your Me.Undo suggestion). If I type in another RefNum that is not duplicate then the RefNum for the record is changed. I don't want this behavior but that is a project for another day.

If I click the 'New Record' button on the bottom of the page (as we have been discussing) the duplicate is recognized, I get the intended warning message, the form is cleared, and then nothing. This is where I want the form to pull up the record associated with the RefNum.

 

[theDBguy]

Hi,

Thanks for the additional information. I won’t be able to download your file until later today. Hopefully, someone will take a look in the meantime. If not, I’ll let you know when I get a chance.

Sent from phone...

 

[gakiss2]

You have already helped a great deal and it is very appreciated. I am thinking if we don't come up with something then it might be time to move to a Delete Query that runs when the form is closed or maybe when the user moves to the next field. Our current approach seems cleaner if we can get it to work though.

 

[theDBguy]

Hi,

No problem. In the meantime, please take a look at this old post.

Sent from phone...