UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Runtime Error 3001, Invalid Argument, Access 2013    
 
   
Chuckinoz
post Jun 2 2020, 10:25 AM
Post#1



Posts: 4
Joined: 26-April 20



Hi People I am very new to VBA this is a piece of code I copied From a tutorial.

Private Sub txtEmployeeNumber_LostFocus()
Dim dbKoloBank As Database
Dim rstEmployees As Recordset

Set dbKoloBank = CurrentDb
Set rstEmployees = dbPracBudget.OpenRecordset("SELECT FirstName, MiddleName, LastName " & _
"FROM Employees " & _
"WHERE EmployeeNumber = '" & txtEmployeeNumber & "';", _
RecordsetTypeEnum.dbOpenDynamic, _
RecordsetOptionEnum.dbConsistent, _
LockTypeEnum.dbOptimistic)

If rstEmployees.RecordCount > 0 Then
If IsNull(rstEmployees!MiddleName) Then
txtEmployeeName = rstEmployees!FirstName & " " & rstEmployees!LastName
Else
txtEmployeeName = rstEmployees!FirstName & " " & rstEmployees!MiddleName & " " & rstEmployees!LastName
End If
End If

Set rstEmployees = Nothing
Set dbKoloBank = Nothing
End Sub

All txtBox names are correct, Employees recordset is from Employees Table and First middle and Last name references are spelt Correctly.
When I type Employee # into relative box and tab out I Get Runtime Error 3001, Invalid Argument.
This post has been edited by Chuckinoz: Jun 2 2020, 10:26 AM
Go to the top of the page
 
theDBguy
post Jun 2 2020, 10:47 AM
Post#2


UA Moderator
Posts: 78,496
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess!

welcome2UA.gif

When you get the error message, is there a button called Debug? If so, click on it and let us know which line in the code gets highlighted.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jun 2 2020, 10:49 AM
Post#3


UA Admin
Posts: 37,513
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

One of the best ways to troubleshoot VBA is to place a break point in the procedure and step through it a line at a time. That way you'll find the exact line where the error is raised and have a better focus on what to address.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RJD
post Jun 2 2020, 10:53 AM
Post#4


UtterAccess VIP
Posts: 10,596
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

There are several problems with your code, but beyond that why not just use ...

CODE
Private Sub txtEmployeeNumber_AfterUpdate()
    Me!txtEmployeeName = DLookup("[FirstName] & ' ' & ([MiddleName] + ' ') & [LastName]","[Employees]","[EmployeeNumber]='" & Me!txtEmployeeNumber & "'")
End Sub

This assumes that txtEmployeeNumber is text. If it is numeric, then ...

CODE
Me!txtEmployeeName = DLookup("[FirstName] & ' ' & ([MiddleName] + ' ') & [LastName]","[Employees]","[EmployeeNumber]=" & Me!txtEmployeeNumber)

HOWEVER, if you are simply inserting the employee name into another table, none of this is necessary. Just link to the employee table to get the name and not store it in another table. If it is for display only, then a link to the employees table should give you that as well, or use an unbound textbox with the DLookup.

Just some thoughts ...

(Correction made to indicate txtEmployeeName as the target control.)

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Jun 2 2020, 10:57 AM
Post#5


UA Admin
Posts: 37,513
Joined: 20-June 02
From: Newcastle, WA


Hm. I think there is a typo in your code here.

LockTypeEnum.dbOptimistic

probably should be

LockTypeEnum.adLockOptimistic

One way to avoid problems of this sort is to make sure you include Option Explicit in every module (standalone and form). Make sure all of the code compiles.

Attached File  optionexplicitandcompile.png ( 11.87K )Number of downloads: 0


Also you try to use dbPracBudget as the recordset you open, but it is never Dimmed. However, you do Dim dbKoloBak and never use it.

Again, Option Explicit and Compile prevent this kind of error.
This post has been edited by GroverParkGeorge: Jun 2 2020, 10:59 AM
Reason for edit: additional error found

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Chuckinoz
post Jun 2 2020, 12:04 PM
Post#6



Posts: 4
Joined: 26-April 20



Thanks for all the replies
TheDBGuy. I have debugged the code and stepped through, Also run to Cursor with no problem.
So not sure what the go is

As for other replies i will Look at all proposed fixes and get back. 1 oclock in the morning here in Australia so need to go to sleep soon
Thanks once again everyone and will get back soon
Go to the top of the page
 
BruceM
post Jun 2 2020, 01:56 PM
Post#7


UtterAccess VIP
Posts: 8,137
Joined: 24-May 10
From: Downeast Maine


Why the Lost Focus event? It may help if you describe exactly what you are trying to do.

Stepping through the code line by line may be helpful. This is done as described in Method 3 at this link.

For variable declaration, you can require it for new modules by going to Tools >> Options in the VBA editor, and check Require Variable Declaration. I strongly recommend requiring declaration, as George indicated.

This will not change any existing modules. For those you just need to type Option Explicit manually.
Go to the top of the page
 
Chuckinoz
post Jun 3 2020, 09:54 AM
Post#8



Posts: 4
Joined: 26-April 20



Sorry people I will try to explain myself a bit better, still need to learn how to post code properly. Yes it is Option Explicit i just copied the code from the Form Account Deposit Module which has other code in it.
I have A Deposit form that has an unbound textBox named txtEmployeeNumber, next to that I have Another unbound textBox named txtEmployeeName. The aim is to put the Employee Number into txtEmployeeNumber and when it tabs out the Employee Name (from Employees Table) goes into txtEmployeeName.

As previously stated this code is from a Project called KOLO BANK in a tutorial book.

I really do appreciate your input. Also George I must of made a typo with dbPracBudget that is another project i am working on.
Attached File(s)
Attached File  Annotation_2020_06_03_225258.png ( 30.79K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Jun 3 2020, 11:27 AM
Post#9


UtterAccess VIP
Posts: 10,596
Joined: 25-October 10
From: Gulf South USA


Hi again: If your purpose is simply to insert the employee name into the unbound txtEmployeeName control on the form when the txtEmployeeNumber is inserted, then my suggestion of using DLookup directly in the txtEmployeeName control will work...

=DLookUp("[FirstName] & ' ' & ([MiddleName] + ' ') & [LastName]","[Employees]","[EmployeeNumber]='" & [txtEmployeeNumber] & "'")

(Assumes the EmployeeNummber is Text. If numeric, remove the single quotes from the WHERE clause.))

If, instead, your objective is to practice using VBA and a recordset to do this, then you are going the hard way, but it can be done. Depends on whether your objective is result or methodology.

See the attached demo. Use Employee Number 12345 or 54321 and also see how a missing MiddleName is handled.

And let us know if this meets the requirement or if you still want to use the VBA/recordset approach anyway. That alternative is certainly possible, even though unnecessary.

HTH
Joe
Attached File(s)
Attached File  InsertEmployeeName.zip ( 19.61K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Chuckinoz
post Jun 3 2020, 10:25 PM
Post#10



Posts: 4
Joined: 26-April 20



Excellent this will do the job fine thank you. Just for interest sake the Tutorial is from an ebook from FunctionX in Newcomers reading list Here https://www.UtterAccess.com/forum/index.php...owtopic=1997455.

Once again thanks for your help
Ralph
Go to the top of the page
 
RJD
post Jun 3 2020, 10:48 PM
Post#11


UtterAccess VIP
Posts: 10,596
Joined: 25-October 10
From: Gulf South USA


You are very welcome - from all of us. Glad that satisfies the requirement. Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 04:07 AM