I have a VBA application i am writing.
I am trying to use an sql statement with docmd.runsql to insert a value into my table that matches a particular record. (which will be the one open). For testing purposed i have stripped down my code.
When running the code it works, however it puts chinese symbols in all the other fields of the record and dose not insert the record into the CapExFileName Field. Then when you try and delete the record it comes up with no search index found.
Any idea on why it would be doing this ?
here is the code.
.......
Dim SQLstring As String
SQLstring = "UPDATE Assets SET Assets.CapExFileName = 'Test' WHERE Assets.Barcode = 'Testies1234'"
im creating a small database and am extremely new to vba, ive got stuck on running a select query from within the vba code itself, the book im currently reading while learning about this stuff suggests my code whould work but after looking into it i believe its wrong as ive read various threads saying you cannot use docmd.runsql with a select query, just wondering if anyone could help and throw some light on how to get this little bit of code working. below is the part of my code that falls over,
basicaly it should lookup the weight based on what the parcel type is and find the price, the parcel type is worked out earlier in my code and is held in strParcelType, theirs probably an easier way to do this as well but have'nt got that far in my book :)
intWeight = Me.txtWeight
Select Case intWeight Case 0 To 100 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[0-100g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 101 To 250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[101-250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 251 To 500 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[251-500g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 501 To 750 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[501-750g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 751 To 1000 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[751-1000g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 1001 To 1250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[1001-1250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" End Select
Me.txtEstimate = strParcelType & intCost
many thanks to any gurus who can point me in the right direction or show me a easier way to do this
After a hiatus of a few years I find myself back writing Access applications, so I need to get caught up a bit.
First of all help me out with the whole ADO, DAO RunSQL thing. In the past whenever I needed to do any database operations I almost always used straight SQL with DoCmd.RunSQL, e.g.:
lsSQL = "INSERT INTO tblUsers CenterID, WorkerID..." DoCmd.RunSQL (lsSQL)
Most other coders seem to use some recordset approach, e.g:
The only time I ever used recordsets was when I needed to loop through each record and apply some logic that was too convoluted for SQL or at least too convoluted for me to write in SQL.
So, what's the advantage of using recordsets - whether ADO or DAO - over RunSQL?
I have a table with one entry- just a date that holds the last day of the month for a function that reminds the user to do something. I have some code that's supposed to change this entry via an update query- but it's not working- literally, no errors, just nothing. I tried running it in an actual query- but still nothing. The query should update the table endCurrMonth to the last day of the month. I've tried hardcoding various dates- still nothing DoCmd.RunSQL ("UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()), Month(Date()) + 1, 0)") anyone know what I'm doing wrong? thanx
i have two tables table1,table2 each with one same field Country.
In my RunSQl query, i want to delete all records in table1 and table2 simultaneously from one button with a certain country. The problem is how can i do this from may be one RunSQL statement i tried to use two deletion runSQl commands, but only one is executed. Actually the one that comes second. If i put doevents in the middle of these statements, only the first is executed.
Here is my code:
Private Sub RemoveCtry_Click() docmd.setwarnings false Docmd.RunSQL "DELETE * FROM table1 WHERE (Country='" & USA & "')" Doevents Docmd.RunSQL "DELETE * FROM table2 WHERE (Country='" & USA & "')" docmd.setwarnings true End Sub
if I use One Statement:
Docmd.RunSQL "DELETE table1.Country,table2.Country FROM table1,table2 WHERE ((table1.Country='" & USA & "' ) AND (table2.Country=' " & USA & " '))" I get an error that i have to specify the table to delete from!!!!
I am trying to develop a page where users can click on alphabets to look for a company name that starts with the selected letter. Searching through the forums i came across the method of using DoCmd.
alpha = Request.QueryString("alp") if alpha <> "" then DoCmd.ApplyFilter "", "[com_name] Like ""[" & alpha & "]*""" end if
The 'alpha' variable here holds the alphabet selected by user from another page. However, I'm getting an error message saying
Microsoft VBScript runtime (0x800A01F4) Variable is undefined: 'DoCmd'
Initially i tried the usual filtering
if alpha <> "" then rs.Filter = "com_name LIKE " & alpha end if
The pages are suppose to display records in 10s. But instead of showing only the records of company starting "A" (example) it shows everything.
Please kindly point me to the correct direction to solve this problem.
Can someone tell me the difference between 'Call' and 'DoCmd' and how each are executed. For my level of programming, an newbie at that, I've used it interchangably so far without a problem. But I'm beginning to think that there is a difference. I don't want to later have to go back to redo my code down the line. I'm beginning to think that I've been very lucky so far in being able to use it interchangebly and my luck will run out soon.:eek:
I need to execute an append query from my vba code but I have to pass in a parameter. I can't set the parameter equal to a form control. I have to actually pass in a value. I don't want to use a querydef because I don't have any result sets.
I have got an unbound access form, and in this form I gather 2 dates (i.e. through txt fields). Once I have these fields, I want to open a form that has one list box on it. This list box should populate based on the dates that I gather on the previous form. How do I specify this through the DoCmd.OpenForm arguments? Currently I have the following code, but it is not working:
Form 1: Dim whereClause As String whereClause = "SELECT * FROM qryInvoice WHERE tblInv.InvDate Between #" & txtStartDate & "# And #" & txtEndDate & "#" & ";" DoCmd.OpenForm "frmInvoiceFax", acNormal, , , , , whereClause
Form 2: public Sub Form_Load(args As String) MsgBox args lstInvoice.Rowsource = whereClause End Sub
I know I am not doing it the right way (because it is not working), but I can't actually find how to do it. Help!
I have a form(frmGetRecord) with a subform(frmSubGetRecord). frmGetRecord has a combobox cmbCNO to choose the patient. The subform displays the admit date and discharge date(if there is one). There can be multiple admit/Discharge dates for a patient. Based on the values from fields on the form and subform, I would like to open a data entry form(frmEvents) for the particular record. I have a command button to run the code to open frmEvents. I am having trouble with the criteria in the where condition.
When I click on the command button I get a type mismatch error. If I test the StLinkCriteria separately, the DoCmd.OpenForm will work for just the CNO field or just the AdmitDate field. If I test those two strings together, I get the type mismatch. Also, DischDate could be blank and I'm not sure how to add an IIf statement to the string to test for that in the where statement. I'm also not sure how to make sure it picks the correct record if there is more than one admit date. I would like to select the desired date record, then have the command button open the appropriate record.
Here is a strange problem I had never encountered before.
I have a form with a few command buttons. One button runs a Docmd.RunSQL code which append some records to a table. Another button runs a Docmd.RunSQL code that delete some records from the same table. The table is a simple table with only 5 fields.
The problem is after I opened the form I can only use one of the above buttons ONCE, after that I got this error message on all codes that began with Docmd.:
Run-time error '2486': You can't carry out this action at the present time.
AND
I could not close the form. I could only close the databse file but could not exit MsAccess. I had to use Task Manager to end MsAccess process.
I had searched several forums but could not find any clue. Hope that I don't have to do the form from scratch again. Thanks for any help.
I have a group form with extra buttons to print, review a report or close the form.
If I use a Macro then I can close the form. I converted the macro to code and I get the error message: A problem occured while Microsoft Access was communicating with the OLE server or ActiveX Control *The expression may not result in the name of macro, ........... *There may have been an error evaluating........
I am making a form that has been set up to have a number of tabs so I can have more then 1 page on the form.
One of the tab pages is used as a search form.There is a couple of textboxes and a listbox where the results end up What I am trying to do is have a double click set up on the listbox so I can double click the result I want and have the forms ID goto that record.
Its a bound form. The primary key is called PersonalID and its bound to a txtbox called txtPersonalID.
this is my code but its not working
Private Sub lstSearchResults_DblClick(Cancel As Integer)
Dim strPersonalID As String strPersonalID = Me.lstSearchResults.Column(0) DoCmd.GoToRecord acActiveDataObject, "PersonalID", acGoTo, strPersonalID DoCmd.GoToControl "pgePersonalInformation"
End Sub
When I try run the code I get an error 2489. saying The object 'PersonalID' isn't open.
however, i realised that the format of the excel spreadsheet (excel version 5.0/95) that i have exported is slightly different from the format of the spreadsheet if i had exported by clikcing on FILE, EXPORT...
how can i specify the excel version for the exported file? i wan it to be in the latest excel version else i am unable to perform some of the marcos i have written in the latest excel format?
i have a listbox that manipulates records on another form but in case i click on one of the records in the listbox that doesnt have any corresponding record in the other form then instead of it saying that it cant locate the record i would like the form to go to a new record....
I am trying to find a way to use the "DoCmd.SendObject acReport" feature WITHOUT using Outlook. Does anybody know of any code or a setting that will enable me to specify another e-mail program, such a Thunderbird?
I should add that I am using Access 2002, SP3, running in Windows XP Professional, Version 2002, Service Pack 2.
Err_mail_Click: MsgBox Err.Description Resume Exit_mail_Click End Sub
As can be seen there is one report which gets attached to email message. But here I want to have one condition, means if [Forms]![frmLogon]![Project]="P-159" then this attachment is ok else [Forms]![frmLogon]![Project]="P-172" then it should change stDocName to another report.
