I have a python script "runAll" that takes two arguments: processID (the primary key of one table as a string) and a filename (a full file name path as a string).
Is it possible to run a python method with arguments from inside a VBA script (activated by a button press)?
My python script is called XMLGenerator.py, and the method I want to run is called runAll(processID,filename).
I have a after update event that will match the written record with any exist record in a table in the field "OrgName". If it doesn't find exact match, will call a function with a "soundex" algorithm to see if there is only a misspelling or another name altogether.
In the afterupdate event, I have a string called strOrg (wich is the name I want to compare).
I have the soundex function in a module, so I can use it for several form generally.
What I want is to pass the strOrg to the soundex function, however I don't know how to declare the variables. however I keep having this error:
"compile error: Argument not optional"
and goes to the line tagged as 1 in the private sub afterupdate
The afterupdate sub is the following:
Code:
Private Sub tOrgName_AfterUpdate()
Dim strOrg As String
strOrg = Me.tOrgName.value
If IsNull(DLookup("orgID", "torg", "OrgName = '" & strOrg & "'")) Then
resMsg = MsgBox("This organization name is not in the list. If you want to look for similar names press YES, if you want to register a new organization press NO.", vbYesNoCancel, "Organization not found")
[Code] ....
and the soundex function is declared as
Code:
Public Function Soundex(strOrg As String) As String
Dim Result As String, c As String * 1
Dim Location As Integer
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then
MsgBox "Go Away", vbOKOnly
Else
Go on and do the real work
End If
End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then
MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code:
Dim varDate
varDate = Date
If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then
MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
Access 2010
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo
Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents
Field: RankOrder
Here is what I have:
Code:
Private Sub RankOrder_BeforeUpdate(Cancel As Integer)
Dim lngRankDup As Long
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0))
If lngRankDup <> 0 Then
MsgBox TestEventID & " already exists in the database"
End If
End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
I have tried:
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Me.RankOrder, 0))
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & [RankOrder], 0))
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!sfTestInfo!Form!RankOrder, 0))
But nothing seems to work.
I have the below script:
For Each Item In offexchfldr.Items.Restrict("[SenderName] = '" & frmfm & "'") ' Select Items that match Sender Name on form
If Item.TaskSubject = subid Then
Item.Categories = frmcat ' Update category from form to outlook
Item.Save
End If
Next
This script checks every item in the outlook inbox where the item.SenderName = my database sendername
This works every time except for when the sendername has quotes or single quotes in their name. So i am trying to utlize the replace method on the item sendername before trying to find the items in the inbox but its not working.
Below is my attempt that does not work.
For Each Item In offexchfldr.Items.Restrict("Replace([SenderName],',"") = '" & frmfm & "'") ' Select Items that match Sender Name on form
On my form I have a set of fields that can be displayed as editable or read-only depending on a "Lockout" checkbox for that record.Is there an easier way of doing this? If not, would it be smarter to create a function to do this? As it stands I'm assuming I would have to have this code run when the form loads, when the Lockout box is clicked, and whenever the record is changed just to ensure that the records are displayed as locked or unlocked appropriately.
Here's what I have:
Code:
Private Sub Lockout_Click()
If [Lockout] = True Then
Me![Customer_Text].Enabled = False
Me![ReqDesc_Text].Enabled = False
Me![MoreInfo_Text].Enabled = False
[code]....
i have the following code in a function but when i want to compile it i have the following code will be blue i have error in this line:
.NamedParameters = True
the error message is this :
method or data member not found
my code is as following:
Function Get_Bearbeiter_Detail(ByVal vBearbeiter_Code As String, Optional ByRef vBearbeiter_Name As String, Optional ByRef vReadOnly_Flag As Boolean, Optional ByRef vAnalyser_Flag As Boolean, Optional ByRef vAdmin_Flag As Boolean) As Boolean
On Error GoTo Err
DoCmd.Hourglass True
Dim rst As ADODB.Recordset
[code].....
I'm writing some simple code that will filter the records on a form:
Option Compare Database
Private Sub cmdOpenByAnalyst_Click()
cmbStaffNames.SetFocus
DoCmd.OpenForm "BICReviewForm", acNormal, , "Staff Assigned=" & "'" & cmbStaffNames.Text & "'"
End Sub
Unfortunately, I keep getting a run-time error (3075) that says "Syntax error (missing operator) in query expression 'Staff Assigned = XXXX"
My question is which method of finding the last record is best, QueryDef or Recordset? Here is my data:
Table:
tbl_module_repairs
Field:
aps_rma
Textbox to insert last record RMA into:
txt_test
Here is some code I tried but get an invalid argument msgbox:
'''''''''''''''''''''''''''''''''
'Opens last RMA into textbox (For opening tag sheet)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
'Get the database and Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_module_repairs")
[Code] .....
I am attempting to pull data from an XML file via VBA without doing the import on external data. I created a blank table called "tblHolding". In theory, my code works the way i want which is: User clicks button, file explorer opens for them to select the file, imports data to "tblHolding", and msg box saying complete.
It runs through the code but doesn't import. It errors at the below code. Also, where do i reference the table so I can append the data.Fails saying method importxml of object application failed
Code:
Application.ImportXML strPath & strFileList(intFile), 2
Code:
Private Sub Command234_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim strPath As String ' Path to file folder
Dim f As Object
[code]...
I have a strange behaviour on a module which reference to Microsoft Outlook Object Library 14.0. The below code got an error 461 on the folderoutlook.display code,i.e. the method is not found. The funny thing is that the same code gives no problem in another accdb file, exactly same code with same reference on same machine.
Set appOutlook = GetObject(, "Outlook.Application")
Set appOutlook = New Outlook.Application
Set namespaceOutlook = appOutlook.GetNamespace("MAPI")
Dim folderOutlook As Folder
Set folderOutlook = namespaceOutlook.GetDefaultFolder(olFolderInbox)
FolderOutlook.Display
How come its so hard to create an undo method when using a form and subform, lookup tables and tiered selections in combo boxes?
View 3 Replies View RelatedCompile error: Method or data member not found
Code:
Option Compare Database
Option Explicit
Dim intItems As Integer
Dim strName() As String, datBirthDate() As Date, intAge() As Integer
Private Sub Form_Load()
intItems = Val(InputBox("How many names do you have?", "Name List"))
[Code] .....
I have a problem in multi user enviroment, with finding and locking a record in database.When one user finds, blocks and edit record ... another user in the same time can find the same record and overwrite it (User and Status values). It is just like he didn't see the changes making by another user.Each user have a copy of the front-end, back-end is on the shared disk.Here is the code to find new record:
Code:
...
Set rs = db.OpenRecordset("tblTable1", dbOpenDynaset)
rs.FindFirst "[Status]='New' And [User] Is Null"
ws.BeginTrans
rs.LockEdits = True
rs.Edit
rs![User] = username
rs![Status] = "Assigned"
rs.Update
ws.CommitTrans
rs.Close
I have a module in my database that creates an appointment based on a table of sessions. The module works in all respects, except two:
1) When I declare optional attendees it sometimes shows them as required attendees in the actual meeting in outlook, but not always
2) The .Send method does not seem to work. It creates the appointment on my calendar as the originator of the appointment, but does not send to the other recipients.
Below is the code:
Code:
Private Sub SchedOutlook()
Dim rsEmployee As DAO.Recordset
Set rsEmployee = CurrentDb.OpenRecordset("Select * FROM Employee INNER JOIN tblSession ON Employee.EmpID = tblSession.EmpID;", dbOpenDynaset)
Dim rsMentor As DAO.Recordset
[Code] ....
Why I am not able to get all records by excluding the Numrows argument in the getrows method?
Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
myrset.MoveFirst
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows(myrset.RecordCount))
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows())
End Sub
Second MessageBox is giving a wrong value.
Is it a mandatory one? Or Do I have to do some ritual like (Movelast) before that?
(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)
I've created an access chart to show total sales ordered by customers.
I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.
This query is then used on a report to create the graph.
Finally there is a button on my form that prints this report.
My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.
If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.
*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code:
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.screenupdating = False
excelApp.Visible = False
Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
Hello,
I want to be able to pass arguments to an access file on start up. I want to be able to grab this value and then perform an action based on this.
Background:
The program will send an email with details for a change request, the recipient will then need to click on a link to accept or reject this change. So I want them to be able to click the link which will trigger the program to save accept or reject.
Regards,
Todd
Hi.
I'm putting together a database of property clients and i have almost finnished but as always i seem to get so very close to the end of a project and i hit a wall.
background:- I have about 5 different tables which mostly have relationships but there is one main table (clientDetails) which holds the main record this has a very predictable clientID primary Key. the other tables have equally predictable keys for the tables ie: LettingID for a letting client insuranceID for insurance clients etc.. the deal is really that EVERYONE is a client so they get the main details filled out and another record in another table using the primary key of the main table in all other tables. one client can have many other records relating to them useing the primary key.
The Problem:- I have made lots of nice forms that all link up together so you can edit and brose all of the records in the database, and this works very well.. however my problem comes when i want to add a new record in one table that relates to the same record in another using the forms not the table view. eg. on form 1 (clientDetails) i add a new client to the database all fine even using the autonum for the PK. now i bring up the record of that client and i have a button (addProperty) this brings up another form (propertyDetails) using the PK from the previous. this works fine if there is already an entry in the DB but if not then the PK is autonumbered..
What i need:- I need to be able to open up a form using PK then (addProperty) button to the next form which finds that record using the pk (clientID) and FK (propertyID) and if there is no FK relating to the PK then to autonumber and use that... so i basically transfer the PK from form 1 to form 2 regarless of any other data relating to it, and auto numbering to the next number in that table for the FK.
eg.
form1 form 2
------- > ---------
PK=100 PK=100
FK=5
this is probibly really basic.. but i hope someone can help ..
Many thanks.
chiller149@gmail.com = is me..
Hi,
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'm going nuts here...can you privide some help.
I'm trying to QUERY an ACCESS database called ARQUIVO and i would like to have from the column EMPRESAS all the DISTINCT records that have the same 'aviacao' in the INDUSTRIA column.
and it goes like this:
Code:<%Set rse = Server.CreateObject("ADODB.Recordset")sSQL = "SELECT DISTINCT empresa FROM arquivo WHERE industria='Aviacao'" rse.open sSQL,con, adOpenStatic, adLockPessimistic, adCmdText%>
But all I get in a 500 error.
The fact is that if I use "*" instead of "empresa" the query runs but i list all the records with 'aviacao' in INDUSTRIA
I have this SQL query made in ACCESS - that does what I want - but it doesn't rune in mine ASP page.
SELECT DISTINCT arquivo.EMPRESA, arquivo.INDUSTRIA
FROM arquivo
WHERE (((arquivo.INDUSTRIA)="Cāmaras Municipais"));
Would anyone help on this?
Can you help me? I get the error wrong type of arguments in my function as follows
Private Sub Command0_Click()
Call ANewDBWithPass("c:BELest", "secret")
End Sub
Function ANewDBWithPass(ByVal tName As String) '// adds the current date to the name of the database
Dim wsp As Workspace
Dim db2 As Database
Set wsp = DBEngine.Workspaces(0)
If Right(tName, 4) = ".mdb" Then
' Remove .mdb from the name
tName = Left(tName, Len(tName) - 4)
End If
' Add date and extension
tName = tName & Format(Date, "dd-mm-yyyy") & ".mdb"
' Create database
',Set db2 = wsp.CreateDatabase(tName, dbLangGeneral)
Set db2 = wsp.CreateDatabase(tName, dbLangGeneral & ";pwd=" & strPassword)
db2.Close
End Function
I have a database to monitor incoming emails from our contractor & my superior wants it to have control like the following situations:
1. If no respond yet, to calculate today's date minus received date
2. If responded, to calculate respond date minus received date
If i use the following expression, it does not relate to if the emails have no respond yet or have been responded.
IIf(IsNull([Reply Date]),DateDiff("d",[Due Date], Now()))
The procedure call "Call RearrangeCoordinates(RearrangeCoords(), ReturnCoords())" return a message "Type mismatched: Array or user-defined type expected"Are there any views as to why the message is being displayed?
Private Sub CommandButton1_Click()
Dim ReturnCoords As Variant
Dim ...........................................
ArrayLength = UBound(ReturnCoords)
[code]....
Is there a command line argument to open AND close a db? I am running a schedule task to open a db which has an autoexec macro but I don't want the db to stay open after running the macro. Or is there a close db function in a macro? I see close form but not db.
View 2 Replies View Related