Solved

Insert Record into Table Using VB Code

Posted on 2008-06-22
29
3,474 Views
Last Modified: 2013-11-27
I have a button on my form that automatically generates a report based on a query.  Except for the address field (which should be filled in by the system) the data on the report is all previous data entry done by the users.  I want to fix it so when the user clicks the button the system first looks in tblNames for the individual on whom the report is being run and checks to see if the address field is blank.  If so the system should assign the next available address in qryAddresses to the field tblNames.Address for that individual and then print the report showing the address that was just saved in the table.  In other words the report should never open with the address blank. The only time that field will be blank is the first time the user runs the report.  The system will then assign the address if its blank.  If the address field is not blank when the user clicks the button the code will continue and simply open the report with the address previously assigned.

Private Sub Command296_Click()
On Error Goto Err_Command296_Click

Dim strSQL As String
Dim CurAdd As Integer
Dim SysAdd As Integer

CurAdd=DLookup("[Current Address]","qry4EMrpt", "[EMNumber]= ' " & Forms!sfrmEmg![EMNumber] & " ' And [EML]= ' " & Forms!sfrmEmg![EML] & " ' ")

SysAdd = DLookup ("[SysAddNumber]", "qrySysAddresses")

If IsNull(CurAdd) then
strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE [Name Number]=[Name Number];"
CurrentDB.Execute strSQL
End if

Docmd.openReport "rptEmn", acNormal

Exit_Command296_CLick:
Exit Sub

Err_Command296_Click:
Msgbox Err.description
Resume Exit_Command296_Click
End Sub

Now I get the error message " You canceled the previous operation."  Nothing else happens.  Any ideas?
0
Comment
Question by:yoducati
  • 15
  • 9
  • 5
29 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21841554
where is the value of [Name Number] coming from?

do you have a field named [Name Number] in table SysAdd?

strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE [Name Number]=[Name Number];"
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21845071
shouldn't your query be something like this:

If IsNull(CurAdd) then
strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE qrySysAddresses.[Name Number]=[Name Number];"
CurrentDB.Execute strSQL

I am assuming that the field [Name Number] is one of the fields returned by the query "qrySysAddresses", and that you want to insert the SysAdd value from qrySysAddresses into tblNames for each record found by qrySysAddresses where the [Name Address] value in qrySysAddresses matches the value submitted to the funxtion.  Is that correct?

AW
0
 

Author Comment

by:yoducati
ID: 21846694
I guess I need the following :
Dim NNum As Integer
NNum = DLookup("[Name Number]","qry4EMrpt", "[EMNumber]= ' " & Forms!sfrmEmg![EMNumber] & " ' And [EML]= ' " & Forms!sfrmEmg![EML] & " ' ")

Im a novice at SQL so my statement is probably incorrect based on both of your comments.  What I want is for the system to lookup the CurAdd and see if its blank.  The CurAdd is coming from the same query that is generating the report.  The qrySysAddresses does not have [Name Number] in it.  Its simply a query to look up all available system addresses.  So what I want is if the CurAdd field is blank when the user tries to open the report the system should look up the next available address in qrySysAddresses and save it in tblNames where tblNames.[Name Number] = qry4EMrpt.[Name Number].  I hope that makes sense.
0
 

Author Comment

by:yoducati
ID: 21846721
Sorry my last line should be

and save it in tblNames where tblNames.[Name Number]=NNum.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21846893
ok, then the SQL statement should read:


Dim NNum As Integer
NNum = DLookup("[Name Number]","qry4EMrpt", "[EMNumber]= ' " & Forms!sfrmEmg![EMNumber] & " ' And [EML]= ' " & Forms!sfrmEmg![EML] & " ' ")

If IsNull(CurAdd) then
strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE tblNames.[Name Number]= " & NNum & ";"
CurrentDB.Execute strSQL

AW
0
 

Author Comment

by:yoducati
ID: 21848774
I still get the error "you canceled the previous operation".  Not sure what that means.  Could it be the something to do with the order in which my code is written?
0
 

Author Comment

by:yoducati
ID: 21848962
Disregard my last post.  I had a field name typed wrong.  With that corrected I now get the error message "Invalid use of Null".
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21850945
the SQL should read:

strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE [Name Number]= " & NNum & ";"
0
 

Author Comment

by:yoducati
ID: 21851431
I have that but i still get the error message "invalid use of null".  i havent figured out if its because of the code If IsNull(CurrentAddress) or if its because there is no data in that field.  If its because there is no data I have a big problem because thats the whole point of doing this i.e.- have the system check to see if the address is blank and if it is select one.  If its the format of the code I dont know what Im doing wrong because Ive used IsNull before with no problems.  Ive tried other methods but cant get any of them to work so far which makes me wonder if its because the field is blank.  Thanks for helping, this is driving me bonkers!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21851554
put a breakpoint on the line that reads

strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT SysAdd FROM qrySysAddresses WHERE [Name Number]= " & NNum & ";"

then you can print out the value of strSQL.

From that string, you can determine the part of the string

"SELECT SysAdd FROM qrySysAddresses WHERE [Name Number]= " & NNum & ";"

you can then copy that SQL into the SQL window of the Access Query builder, and determine what  the value returned for SysAdd is.  That will tell you if you are getting a null or not.

AW
0
 

Author Comment

by:yoducati
ID: 21855074
Ill see if I can figure anything out with that solution.  I cant even get the following to work though:

If IsNull(CurAdd) Then
msgbox "test", vbokonly
end if

I still get invalid use of Null with this code so i think it might be something here.  
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21855208
yoducati,

post all the codes you are using for this sub
0
 

Author Comment

by:yoducati
ID: 21855465
Private Sub Command 296_Click()
On Error Goto Err_Command296_Click

Dim strSQL As String
Dim CurAdd As Integer
Dim SysAdd As Integer
Dim NNum As Integer

NNum = DLookup("[Name Number]", "qry4EMrpt")
SysAdd = DLookup("[SysAddNumber]","qrySysAddresses")
CurAdd - DLookup("[Current Address]","qry4EMrpt")

If IsNull(CurAdd) then
Msgbox "test", vbokonly

'strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE [Name Number]= " & NNum & ";"

'CurrentDBExecute strSQL
End If

Docmd.openreport "EMrpt", acNormal

Exit_Command296_Click:
Exit Sub

Err_Command296_Click:
Msgbox err.description
Msgbox err.number
resume Exit_Command296_Click
End Sub

I commented out the strSQL because right now I cant even get the message box to pop up.  I know CurAdd will be null sometimes which is the whole point of what Im trying to do, so Im assuming its something to do with "IsNull" instead of NZ or another method, or maybe its the timing of the code?  

Right now when the current address in the table is null I get the error message "invalid use of Null" and Error Number "94".  when the field has a value the code performs as expected.  Also the qry4EMrpt will always return only one record and it will always be for the individual on whom I want to report.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21855852
try adding the nz


CurAdd =nz(DLookup("[Current Address]","qry4EMrpt"),null)

or

CurAdd =nz(DLookup("[Current Address]","qry4EMrpt"),"")


If IsNull(CurAdd) or len(curAdd & "")=0 then
Msgbox "test", vbokonly

also you cannot use insert in this statement

'strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE [Name Number]= " & NNum & ";"

you have to use update statement

strSQL= "update tblNames set [CurrentAddress]= qrySysAddresses.[SysAdd] WHERE [Name Number]= " & NNum & ";"


0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:yoducati
ID: 21855965
O.k.  I tried :
CurAdd =nz(DLookup("[Current Address]","qry4EMrpt"),null)
and I get the same message "Invalid use of null"

when I try :
CurAdd =nz(DLookup("[Current Address]","qry4EMrpt"),"")
I get the message "Type Mismatch"

I changed the SQL but left it as a comment for now.  i figure I should get the message box to show up first so I dont confuse the issue.  Any ideas on the next step?


0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21856021
Since CurAdd is declared as an Integer, the only way it can be processed as Null is if it has NEVER been assigned a value.  A Function that returns an Integer value will always return a numeric value.

Try this change:


'Assign an initial (Default) value of -1 to CurAdd
CurAdd = -1
NNum = DLookup("[Name Number]", "qry4EMrpt")
SysAdd = DLookup("[SysAddNumber]","qrySysAddresses")
CurAdd = Val(DLookup("[Current Address]","qry4EMrpt") & "")

If IsNull(CurAdd) Or CurAdd <= 0 then
Msgbox "test", vbokonly

'strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE [Name Number]= " & NNum & ";"

'CurrentDBExecute strSQL
End If

AW
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21856026
can you attach your db. do a compact and repair first. if db is >4mb zipped it first.

check the Attach File below.
0
 

Author Comment

by:yoducati
ID: 21856043
O.k. how about this?  Im not sure if the code is written correctly but it works for the message box. Just want to make sure its o.k.

CurAdd = NZ(DLookup("[Current Address],"qry4EMrpt"),0)

If CurAdd=0 then
msgbox"test"
End if

0
 

Author Comment

by:yoducati
ID: 21856068
Cant attach it.  Its on a secure network.  I think we are getting close though.  If you think the code I posted is ok, Ill take the comment off the strSQL and see what happens.  
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21856162
<Cant attach it.>..never mind

so, CurAdd  is number type with default value of zero..

do you want to insert(add record) or Update the record?
0
 

Author Comment

by:yoducati
ID: 21856270
It is a number type, but does not have a default value.  I want the SQL to update the existing record NOT create a new one. At the point the user pushes this button the record will be in the system already, it just may not have an address associated with it.  I just want the system to check and see if the address is blank and if so assign the next available address in the system and then print the report.  In other words I dont want the user to be able to print the report without an address.  Since the system needs to assign the address I figured this would be the best time to do it so the system should update the existing record the user is trying to print with an address if its blank and then print the report with the newly assigned address that has now been updated in the table by strSQL.  I hope that makes sense.  
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21856323
ok..  what are the fields in table tblNames? do you have a unique record identifier field?
post the names of the fields here
0
 

Author Comment

by:yoducati
ID: 21856396
Yes.  The unique ID for tblNames is [Name Number]  the other fields are [First Name], [Middle Name], [Last Name], [Current Address]  

I took out the comment and updated the SQL but now get the message "Too few parameters. Expected 1"  which I think means I have a field named wrong but ive gone through it very carefully and cant find it.  Is there anything else that causes that error?  I think we are close because the message box is working as expected each time I test it.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21856428
again please, post the SQL that is giving the error
0
 

Author Comment

by:yoducati
ID: 21856508
strSQL= "update tblNames set [CurrentAddress]= qrySysAddresses.[SysAdd] WHERE [Name Number]= " & NNum & ";"
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 21856624
use this

strSQL= "update tblNames set [CurrentAddress]= '" & SysAdd &"' WHERE [Name Number]= " & NNum
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21857075
did it work?
0
 

Author Comment

by:yoducati
ID: 21857130
We are on the verge of the solution.  Now the address updates as it should.  Only when the field is blank and it stores the address the system selected in the table.  But when I move to the next individual and try to print the report I get the same message "invalid use of Null".  Do I need to requery anything or add to my code so that the button provides this function for whatever individual the user is trying to print the report for?
0
 

Author Comment

by:yoducati
ID: 21857585
Disregard my last post.  I figured out the new problem.  Its giving me that error message because SysAdd = DLookup("[SysAddNumber]","qrySysAddresses") can sometimes return a null.  This query has criteria that determines what addresses the system can assign. So I added SysAdd = NZ(DLookup("[SysAddNumber],"qrySysAddresses"),0) and added the code to provide a message box if SysAdd = 0 telling the user there are no available addresses.  When there are addresses available to be assigned the system assigns them correctly.    Thanks so much for your help!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now