Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3497
  • Last Modified:

Insert Record into Table Using VB Code

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
yoducati
Asked:
yoducati
  • 15
  • 9
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
Arthur_WoodCommented:
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
 
yoducatiAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
yoducatiAuthor Commented:
Sorry my last line should be

and save it in tblNames where tblNames.[Name Number]=NNum.
0
 
Arthur_WoodCommented:
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
 
yoducatiAuthor Commented:
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
 
yoducatiAuthor Commented:
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
 
Arthur_WoodCommented:
the SQL should read:

strSQL= "INSERT INTO tblNames(CurrentAddress) SELECT (SysAdd) FROM qrySysAddresses WHERE [Name Number]= " & NNum & ";"
0
 
yoducatiAuthor Commented:
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
 
Arthur_WoodCommented:
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
 
yoducatiAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
yoducati,

post all the codes you are using for this sub
0
 
yoducatiAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
yoducatiAuthor Commented:
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
 
Arthur_WoodCommented:
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
 
Rey Obrero (Capricorn1)Commented:
can you attach your db. do a compact and repair first. if db is >4mb zipped it first.

check the Attach File below.
0
 
yoducatiAuthor Commented:
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
 
yoducatiAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
yoducatiAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
ok..  what are the fields in table tblNames? do you have a unique record identifier field?
post the names of the fields here
0
 
yoducatiAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
again please, post the SQL that is giving the error
0
 
yoducatiAuthor Commented:
strSQL= "update tblNames set [CurrentAddress]= qrySysAddresses.[SysAdd] WHERE [Name Number]= " & NNum & ";"
0
 
Rey Obrero (Capricorn1)Commented:
use this

strSQL= "update tblNames set [CurrentAddress]= '" & SysAdd &"' WHERE [Name Number]= " & NNum
0
 
Rey Obrero (Capricorn1)Commented:
did it work?
0
 
yoducatiAuthor Commented:
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
 
yoducatiAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now