Search key niot found in any record? (1 Viewer)

Gasman · Jan 16, 2018

Hi all,
I have cocked up big time.

I was trying to add a date and time stamp for created, amended and by whom.
I first had individual code in two forms and added the new controls and used code like

Code:

Me.CreatedBy = ENVIRON("UserName")
Me.Created = NOW()

However I was a little tight for space on some forms, so *thought* I could do it via SQL in a sub as below

I tested it in another database but just from the immediate window.
Putting it in the needed DB I got the error message that could not update at this time (as i was on the record and calling the code from the Form BeforeUpdateEvent.:banghead::banghead::banghead:
So I have corrupted a record whose keys are linked to other tables, but I thought I could repair at the table level by keying in the correct values for the links, but I get the error message every time I do so and try and save the table.

I have a backup and will go back to that, but want to know if there is another way and what I might possible have done. I've clsoed Access and reopened, but it still happens. Even tried directly on the back end with same result.

TIA

Code:

Sub UpdateTimeStamp(pTable As String, pKey As String, pKeyValue As Long, pAction As String)
' This will update relevant table with Created Date and user or Amended Date and user
On Error GoTo ErrHandler

Dim strSQL As String, strNow As String
strNow = Format(Now(), strcJetDateTime)
strSQL = "Update " & pTable & " "

Select Case UCase(pAction)
    Case "ADD"
        strSQL = strSQL & "SET Created = " & strNow & ",CreatedBy = '" & Environ("UserName") & "'"
     Case "EDIT"
        strSQL = strSQL & "SET Amended = " & strNow & ",AmendedBy = '" & Environ("UserName") & "'"
End Select
    strSQL = strSQL & " WHERE " & pKey & " = " & pKeyValue
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
     
Exit_Sub:
    DoCmd.SetWarnings True
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description & " in UpdateTimeStamp module"
    Resume Exit_Sub


End Sub

Ranman256 · Jan 16, 2018

you do not need code for created date. In the table ,set the field default = NOW()
No code needed.

you must have a key to update records in edit. No key needed for New recs.(append)

Gasman · Jan 16, 2018

Thanks Ranman256,

Another stupid mistake there.:banghead:
I've put the backup in place and recoded for the orginal method for now.

I realise that I could hav set NOW() in the table, but thought it best to keep the code together (at least for now). I have used Date() in other DBs as well.

Learning as I go.

Edit: I tried a compact on what is now the old BE and it now allows the correction of the data.

Ranman256 said:
you do not need code for created date. In the table ,set the field default = NOW()
No code needed.

you must have a key to update records in edit. No key needed for New recs.(append)

Mark_ · Jan 16, 2018

Gasman said:
However I was a little tight for space on some forms, so *thought* I could do it via SQL in a sub as below

So you know, since Access likes you having controls on the screen (especially for continuous forms) for fields that you would otherwise be able to check in the recordset, you can always disable the controls and set their height/width to 0.

I've done this on a couple forms to get around this oddity in Access.

isladogs · Jan 16, 2018

Mark_ said:
So you know, since Access likes you having controls on the screen (especially for continuous forms) for fields that you would otherwise be able to check in the recordset, you can always disable the controls and set their height/width to 0.

I've done this on a couple forms to get around this oddity in Access.

Or set them as hidden and if necessary 'overlayer' them on the form

Mark_ · Jan 16, 2018

Ridders,

I try to avoid that as I'm less likely to remember they are not "really there" when I'm changing the form.

Gasman · Jan 16, 2018

At present it is only on two forms for two tables. The first is a normal form and I found space and hid them. For the datasheet, I put them in the footer on my second attempt, the first was in the detail, also hidden
I realise I could shrink them, but not to 0 I must admit.
I too wanted some evidence of them being there else I would forget them quite easily with my memory.

isladogs · Jan 16, 2018

Mark_ said:
Ridders,

I try to avoid that as I'm less likely to remember they are not "really there" when I'm changing the form.

Surely if the size is reduced to 0 you are more likely to forget them ...

At least if hidden but size >0 they are visible in design view.

Anyway, more importantly Access will remember both methods!

Pat Hartman · Jan 16, 2018

It is NOT necessary to bind every field to a control on a form. This is only a problem with reports and that is because Access completely rewrites your RecordSource for a report because Access thinks it is smarter than you are (OK, maybe not you but it is smarter than me). Therefore, in a report, if you ever need to refer to a field in code, you MUST bind it to a control on the form. You can shrink the control and set its visible property to No but it has to be there.

When I need to use a hidden control on a form or report, I make it as small as I can to keep it from interfering with the layout AND I make it bright yellow. Therefore, when I'm working with the form in design view, I can always see the hidden controls.

Since I modify the Name property of each control on a form or report to have a prefix, it is easy to identify which fields are in the RecordSource but not bound to a control.

Me.LastChangedBy = Forms!frmLogin!txtLoginName

the LastChangedBy field has no prefix so it is not a control but the txtLoginName does have a prefix so it is a control.

Mark_ · Jan 17, 2018

Pat,

I got in the habit when dealing with continuous forms. Like reports, Access gets upset if you try to work with something not bound to a field on the form. As such it is a useful habit to get into.

Pat Hartman · Jan 17, 2018

Not in my world. Fields from a form's RecordSource are always available in code whether or not they are bound to controls. It is only Reports where there is a problem and the problem exists entirely because Access rewrites your RecordSource in a report and eliminates any field not specifically bound to a control.

Edit - changed "forms" to "reports" where appropriate.

Gasman · Jan 17, 2018

Thank you Pat, that is very useful to know. I was obviously under the impression that to use Me.whatever I had to have a corresponding control.

You might wish to amend your post though as you have stated 'only Forms' not 'only Report'?

Pat Hartman said:
Not in my world. Fields from a form's RecordSource are always available in code whether or not they are bound to controls. It is only Forms where there is a problem and the problem exists entirely because Access rewrites your RecordSource in a report and eliminates any field not specifically bound to a control.

Gasman · Jan 17, 2018

OK, now thoroughly confused.:banghead:

Not hard form me I must admit.

I took the little test DB attached and removed the controls mentioned in the thread. This is the DB I used for the query version.
The form frmTable1 works as it did when the controls were present.

So I went into the database where I had added the code, removed the controls from the two forms. Now it will not compile?
Whilst I did rename the fields Created and Amended with the word Date as a suffix and edited the code to match in both DB only the attached works?

All forms have

Code:

Option Compare Database
Option Explicit

isladogs · Jan 17, 2018

Hi
I haven't read the whole thread & I may be misunderstanding your last post
The test database compiles for me (A2010 32-bit)

I've not attempted to do anything else with it
The thread is marked SOLVED so I'm not sure what you're asking ...

Gasman · Jan 17, 2018

Sorry Colin,
I've removed the Solved marker (was not aware you could do that). The original question was solved, however.....

It was following on from what Pat had stated, that you do not need controls for every field on a form post 9.

I removed the controls for the fields from the basic DB and the form works fine, in that it updates the relevant fields in Table1.

I then repeated the process in my live DB and it would not compile, giving an error on Me.CreatedDate in the form's Before_Update event.?

Now I am puzzled as to why it works in one DB and not the other. I do not need the fields to have corresponding controls on the form, just wanted to be able to update them and Pat's solution seemed just the ticket.?

Please look at the basic DB, no controls exist on form frmTable1 for Created and Amended fields in the table, yet they update fine.?

This brings to mind another question, is there a way to get all errors in one compilation, rather than identifying and correcting one by one?

Pat Hartman · Jan 17, 2018

What does the compile error say?

For convenience, Access adds the columns of the RecordSource to the fields collection of the form. This is one of the reasons that I always give my controls a name different from the bound column. So CustName is the bound column but txtCustName is the name property of the control. I can then refer to either by using Me. and you will see both names pop up in intellisense. Once you do that, you have to be careful to only reference control properties via the control NAME (txtCustName).

Gasman · Jan 17, 2018

Hi Pat,

It was (I seem to recall) the standard missing control message. I double checked the spelling of the field and control and have not modified the names since, just readded the controls.
I have just copied the DB, gone into the copy and removed the four controls from one of the forms and it compiles? Opened the DB and the form works fine and updates the fields.

Gone back to the live version and did the same and it also compiles and also updates the fields.?

Not sure what is happening here, but if it fails again, at least I know what to do to correct it.

Pat Hartman said:
What does the compile error say?

For convenience, Access adds the columns of the RecordSource to the fields collection of the form. This is one of the reasons that I always give my controls a name different from the bound column. So CustName is the bound column but txtCustName is the name property of the control. I can then refer to either by using Me. and you will see both names pop up in intellisense. Once you do that, you have to be careful to only reference control properties via the control NAME (txtCustName).

Pat Hartman · Jan 17, 2018

The original change was probably not correct. Good that it is working now.

Search key niot found in any record? (1 Viewer)

Enthusiastic Amateur

Attachments

Well-known member

Enthusiastic Amateur

Longboard on the internet

MVP / VIP

Longboard on the internet

Enthusiastic Amateur

MVP / VIP

Super Moderator

Longboard on the internet

Super Moderator

Enthusiastic Amateur

Enthusiastic Amateur

Attachments

MVP / VIP

Enthusiastic Amateur

Super Moderator

Enthusiastic Amateur

Super Moderator

Similar threads

Users who are viewing this thread