Name_Slug issue on change from 97 to 2003

[Pegsat]

This is my first post ever anywhere on this subject and this site. I am a noob. My Office database was created by an employee a while back. I've taught myself things by trial and error repeating things I see he did, such as adding fields and duplicating code lines to address the new fields. So I express, and apologize that I am far from a code writer.

But my issue is that I had finally changed from Office 97 to Office 2003 last year, and I've come across issues as I use my "converted" database. I've refrained from asking because of my inexperience.

DoCmd.GoToControl "cbx_NAME"
SendKeys NAME_SLUG
SendKeys "{ENTER}"
DoEvents
SendKeys "{NUMLOCK}"

I have removed the sendkeys "{Numlock}" to rid of another debugging issue found. (It was apparently no longer needed.)
The purpose of the name slug (which does show the name to be entered when I mouse-over) is to repopulate the initial part of the form with whom I edited.

Here is the whole command:

Private Sub cb_FINAL_POST_EDITS_Click()
Dim L_NAME_HOLDER, F_NAME_HOLDER, E_mail_Holder, STREET1_HOLDER, STREET2_HOLDER, _
CITY_HOLDER, ST_HOLDER, ZIP_HOLDER, PH_HOLDER, PH_2_HOLDER, Account_Holder, Alt_HOLDER, _
PROMO_HOLDER As Variant ''''Ray added chb_dha
Dim MESSAGE, STYLE, TITLE, HELP, CONTEXT, RESPONSE As Variant
Dim DB As Database, RST1, RST2 As Recordset
Dim DB_REF_HOLDER, DB_REF_STRING, NAME_SLUG, FIND_DB_REF_STRING As Variant

L_NAME_HOLDER = tbx_LNAME.Value
F_NAME_HOLDER = tbx_FNAME.Value
E_mail_Holder = tbx_Email.Value
STREET1_HOLDER = tbx_STREET1.Value
STREET2_HOLDER = tbx_STREET2.Value
CITY_HOLDER = tbx_CITY.Value
ST_HOLDER = tbx_ST.Value
ZIP_HOLDER = tbx_ZIP.Value
PH_HOLDER = tbx_PH.Value
PH_2_HOLDER = tbx_PH_2.Value
Alt_HOLDER = tbx_ALT.Value
Account_Holder = tbx_Account.Value
PROMO_HOLDER = cbx_PROMO.Value

Set DB = CurrentDb
Set RST1 = DB.OpenRecordset("t_CUST_DATA1", dbOpenDynaset)

DB_REF_HOLDER = tbx_DB_REF.Value

FIND_DB_REF_STRING = "DB_REF_NO = " & DB_REF_HOLDER & ""

Set DB = CurrentDb
Set RST1 = DB.OpenRecordset("t_CUST_DATA1", dbOpenDynaset)

With RST1
.FindFirst FIND_DB_REF_STRING
.Edit
![L_NAME] = L_NAME_HOLDER
![F_NAME] = F_NAME_HOLDER
![E_mail] = E_mail_Holder
![STREET_1] = STREET1_HOLDER
![STREET_2] = STREET2_HOLDER
![CITY] = CITY_HOLDER
![ST] = ST_HOLDER
![ZIP] = ZIP_HOLDER
![PHONE] = PH_HOLDER
![PHONE_2] = PH_2_HOLDER
![ALT_ADDRESS] = Alt_HOLDER
![Acct] = Account_Holder
![PROMO_NAME] = PROMO_HOLDER

.Update
End With ''''RST1

NAME_SLUG = L_NAME_HOLDER & ", " & F_NAME_HOLDER

'''CLOSE FORM, RUN QUERY, OPEN FORM
'''FILL cbx_ lookup with NEW ADD...
DoCmd.SetWarnings False
Application.Echo False
DoCmd.Close acForm, "fm_ADD_EDIT_CLIENT", acSaveNo
DoCmd.OpenQuery "q_mt_CUST_DATA2"
DoCmd.Close acQuery, "q_mt_CUST_DATA2", acSaveYes
DoCmd.OpenForm "fm_ADD_EDIT_CLIENT"
DoCmd.SetWarnings True
Application.Echo True
'''message box that new add was made...
MESSAGE = "Edited Fields Successfully Updated..."
STYLE = vbOKOnly
TITLE = "Edits Posted"
RESPONSE = MsgBox(MESSAGE, STYLE, TITLE)
''''SendKeys "{NUMLOCK}"
'''FILL cbx_ lookup with NEW ADD...
'''Const VK_NUMLOCK = &H90
DoCmd.GoToControl "cbx_NAME"
SendKeys NAME_SLUG
SendKeys "{ENTER}"
DoEvents

End Sub

Please forgive me if I didn't post this correctly using "quote".
The last "DoEvents" is probably not necessary, I think it was for the numlock part.
The pic is what it's supposed to look like when it works right.

[/URL][/IMG]

 

[Micron]

Wow. Lots wrong with that code...
Have read your post 3 times and still don't understand what the issue is. Aside from posting a pic of what you want, you need to elaborate on what you're getting in terms of results, error messages/numbers and such, and what the process is. From what little I grasp of what's going on with the code, it looks like it might be an over-blown way of editing a table by creating a DAO recordset when this could be done simply by calling an Update query. I'll reserve my judgement on that after I know more about what the problem and process is.

 

[Norie]

If you want to put a value in a field/control there should be no need for SendKeys, you should just be able to write directly.

 

[Pegsat]

Reply to Micron:
Oh, sorry. The error is: Run-time error 70: Permission Denied.
The purpose of the name slug (which does show the name to be entered when I mouse-over the yellow highlight) is to repopulate the initial part of the form with whom I edited, as in the pic above. It used to pull back up the name (from a drop down list derived from a query of the cust_data2 table.) that I just edited and display the vitals of that customer. It saves me from re-entering the name for verifying those changes took place and inspection for other mistakes possibly made.

(Beside the point comment) Yeah, It does seem to make too many steps to do what sounds like a simple process. What is annoying is that the initial cust_data1 table has separate fields (table column) for First and last names, but then turns around to combine those into one column by last name in cust_data2 table... which is what gets displayed in the name field. Don't ask me why he did it that way, he was an odd but good fellow. I believe it was in case I had customers with the same last name but different first names. (Which I do, and it makes it easier to select from the drop down.)

Reply to Norie:
If I delete the sendkeys, I don't get an error, but it doesn't repopulate the fields of the form shown above. I don't know enough to create an alternative function.

Thanks for the replies.

 

[Pegsat]

In the debugger, this what it shows when I mouse-over the defective code. It knows what I want as it shows the customer name, but it doesn't actually do it anymore. (And has the error.)

[/URL][/IMG]

 

[Micron]

OK, this would be a band-aid solution because as Norrie and I are claiming, the approach is just - weird. There are better ways, like executing an update query or interacting directly with the form controls, which perhaps is one reason I've very seldom used SendKeys. AFAIK, your issue has more to do with later versions of Windows as opposed to your Office version upgrade, but perhaps they go hand in hand. Anyway, here is a patch that has worked for others:
Dim WshShell As Object
Set WshShell = CreateObject("WScript.Shell")
Put the red part below your last Dim statement line at the top.
Use this syntax for any SendKeys line for keyboard keys:
WshShell.SendKeys "{ENTER}"
or like WshShell.SendKeys NAME_SLUG

NOTE: you have to set a reference to MS Scripting Runtime
I can't take credit for the suggestion - found it here https://social.technet.microsoft.co...ission-denied-vb6-in-windows-7?forum=Offtopic

 

[Pegsat]

Thank you Micron! It works.
Thanks for the red color and where to place it. Most of what you told me was "over my head", I barely can do queries. I do have a query issue too, that I haven't figured out why it doesn't work now. (Another thread soon.)

I didn't understand about referencing, did search, found some info, but my "Tools, References" was greyed out. So I just tried it anyway and it worked.
I went back into Microsoft Visual Basic and looked again, not greyed, clicked, and found that MS Scripting Runtime was not checked (selected). But since it is working, I left it alone.

You still get the credit. Thank you... and also for being patient with me.

 

[Micron]

Glad to have helped. Maybe we'll get lucky with your query issue too.