Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 11th, 2004, 08:48 AM
Authorized User
 
Join Date: Apr 2004
Location: Montreal, Quebec, Canada.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to lguzman
Default Trying to delete a record... Can't do it...

Good day all.

Here is some background.

I log on a table the Computer Names of all machines which launch my application.

I would also like to delete the name of the computer when they exit the application.

To that effect on my form Form_Close has the following code:

  Dim NBuffer As String
  Dim Buffsize As Long
  Dim Wok As Long
  Dim strComputerName As String
  'Dim wrkDefault As Workspace
  Dim dbs As Database
  Dim rst As Recordset
  'Set wrkDefault = DBEngine.Workspaces(0)
  Set dbs = CurrentDb
  'Set dbs = OpenDatabase("lcdb.mdb")
  Set rst = dbs.OpenRecordset("ActiveMachines")
'---------------------------------------------------------------------------------
'------------------------- Establishes Machine Name
  Buffsize = 256
  NBuffer = Space$(Buffsize)
  Wok = api_GetComputerName(NBuffer, Buffsize)
  strComputerName = Trim$(NBuffer)
'---------------------------------------------------------------------------------
'------------------------- Deletes the Machine Name

  dbs.Execute "DELETE * From " & "ActiveMachines where MachineName = " & "strComputerName;"
  dbs.Close

But it doesn't work. It keeps telling me : "Too few parameters. Expected 1.".


Help!

Thanks.




[LGuzman]
__________________
[LGuzman]
Reply With Quote
  #2 (permalink)  
Old August 11th, 2004, 03:19 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Be careful of double and single quotes:

dbs.Execute "DELETE * FROM ActiveMachines WHERE [MachineName] = '" & strComputerName & "';"

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #3 (permalink)  
Old August 12th, 2004, 10:19 AM
Authorized User
 
Join Date: Apr 2004
Location: Montreal, Quebec, Canada.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to lguzman
Default

Greg.

Thank you very much for answering me. It seems to have helped. I still get an error but in the error message I now see the Computer Name.

Now this is the error I get when I use the line you provided me with:

Syntax error in string in query expression'[MachineName] = 'MTLACCOUNTING'.

If I remove the [] of MachineName I get the same error but without the []...

Thanks again Greg. Hope you or anyone else can help me with this one.






[LGuzman]
Reply With Quote
  #4 (permalink)  
Old August 12th, 2004, 10:31 AM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Read my code again on the web so you can see proper spacing and quotes. If you read this in e-mail, it'll look odd with the codes showing. Copy and paste it into your code exactly as I have it written.

Code:
dbs.Execute "DELETE * FROM ActiveMachines WHERE [MachineName] = '" & strComputerName & "';"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #5 (permalink)  
Old August 12th, 2004, 12:32 PM
Authorized User
 
Join Date: Apr 2004
Location: Montreal, Quebec, Canada.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to lguzman
Default

Thanks for the prompt response. Copy and paste is what I had done. Nevertheless I repeated the excercise and copied and pasted it again. The exact error message I'm getting is:

Run-Time error '3075':

Syntax error in string in query expression '[MachineName] =
'MTLACCOUNTING'.



[LGuzman]
Reply With Quote
  #6 (permalink)  
Old August 12th, 2004, 01:27 PM
Authorized User
 
Join Date: Apr 2004
Location: Montreal, Quebec, Canada.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to lguzman
Default

More info...

I clicked on Debug, and it brought me to:

dbs.Execute "DELETE * FROM ActiveMachines WHERE [MachineName] = '" & strComputerName & "';"

While checking the syntax, I placed the cursor on strComputerName and noticed that its value was "MTLACCOUNTING".

So I got currious, and put the following line right before the above mentioned line: strComputerName="MTLACCOUNTING".

When I did this, it worked. So I think the problem is the  after MTLACCOUNTING.

So the question now would be does anyone know why I get that and more important how can I get rid of it!

Thanks.



[LGuzman]
Reply With Quote
  #7 (permalink)  
Old August 12th, 2004, 01:39 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

How about if you did this? Add the TRIM function to the computer name... that is:

dbs.Execute "DELETE * FROM ActiveMachines WHERE [MachineName] = '" & Trim(strComputerName) & "';"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #8 (permalink)  
Old August 12th, 2004, 02:17 PM
Authorized User
 
Join Date: Apr 2004
Location: Montreal, Quebec, Canada.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to lguzman
Default

Same thing. I still get the "MTLACCOUNTING".

[LGuzman]
Reply With Quote
  #9 (permalink)  
Old August 12th, 2004, 02:19 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Make sure there is no space between the " and the ' in the part of the expression that looks like this:

& "';"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #10 (permalink)  
Old August 12th, 2004, 03:20 PM
Authorized User
 
Join Date: Apr 2004
Location: Montreal, Quebec, Canada.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to lguzman
Default

Checked. No space. Same issue.

[LGuzman]
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I delete a record from a form? dstein4d Access VBA 8 August 24th, 2007 12:09 PM
Delete a record row, not just the record. Coby Access VBA 1 April 30th, 2007 06:29 AM
automatic record delete. joeore Beginning PHP 5 April 29th, 2004 11:27 AM
Can't delete record Trojan_uk SQL Server 2000 3 November 27th, 2003 12:03 PM
Delete record but how? scifo Beginning PHP 3 August 1st, 2003 12:14 PM



All times are GMT -4. The time now is 03:00 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.