Wildcard Search Using Visual Basic Problem
first i must thank everyone here. this forum has proved invaluable for some of the simple databases i have created. now i have a problem which i cannot find the answer for.
i'm using access 2000
basically i am creating a cemetery database so most of the information i'm dealing with is duplicated. to create a unique record i have six primary keys on the one table. ie people with the same name, age, and birthday can be buried in the same grave.
anyway i need to do a wildcard seach from a form (QBF) based on surname and firstname. i have downloaded the code below and modified it:
Private Sub cmdsearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As querydef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT searchtestdata.Surname; searchtestdata.Firstname; " & " FROM searchtestdata"
strWhere = "WHERE"
strOrder = "ORDER BY searchtestdata.autonumber;"
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtsurname) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (searchtestdata.Surname) Like '*" & Me.txtsurname & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & " (Searchtestdata.firstname) Like '*" & Me.txtfirstname & "*' AND"
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("quesearchtestdata")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
'Open the Query
DoCmd.OpenQuery "quesearchtestdata", acViewNormal
the code stops at the line:
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
and tells me either that i have "run-time error 3142 - characters found at end of SQL statement" - this is the error i am recieving at the moment, or more usually " run-time error 3141 - which tells me the SELECT - WHERE statement is wrong.
i am definetly no expert in VB - i am only just starting using it.
so guys any idea?
thanks in advance
Many thanks to those who helped with my last couple of problems.
I've allmost completed my project now and am debugging.
I've found that in order to close one of my forms, which has combo boxes on it, I need to have no values selected in those combo boxes. Could someone give me the visual basic script (or any alternative meathod) for resetting the combo boxes to their default values; i.e. nothing selected in them!
Greetings to all the experts out there,
Here is something I wonder if it has already existed or been asked before, a program or an add-in feature that can compile an Access database (including forms, reports, tables...) to a stand-alone .exe application, just like Visual Basic does. The reason why I think it's doable is because in Access, we use VB codes anyway and it wouldn't need to be rocket-science for Access to do something like that or this would be an improvement for future versions.
There are plenty of advantages of going from an Access form to a stand-alone app, i.e. we wouldn't need codes to hide background and menu or status bar; we wouldn't need to disable mouse-scroll or prevent users to go back and accidentally delete past records; or we wouldn't need to worry about users seeing your terrific coding techniques and on and on.....Agree that all these can be done within Access but imagine how much time it'd save you from not doing all these things.
Thank you all in advance.
I have a user running office 2007, with a program called Dev_Tracker wthat uses a front end of access 2000. This program allows a user to log in their project hours. The skinny is that when they lauch the program and try to make an entry they receive this error message:
Run-time error 3075
syntax error in date in query expression tbl_timesheet.weekof=#2.4.2008# AND tbl_timesheet.user=dworden.
If the user logs into another machine it works fine so it appeare to local to the machine. I have cleaned the registry and done other tweaks but no luck.:confused: Any ideas would be helpful.
hi... i have a form that display things in a table in access. when program the form to delete a query it deletes it but does not do rs.movenext
what i want to do is the form to display the next set of data from the database. i am using dao....
please help me!!
I'm getting the following error when I try and open my database: "The visual basic for applications project in the database is corrupt". I'm pretty sure I know the answer, restore from a back up. The only problem is that the sytem admin usually backs the file up every night and he has not gotten a valid backup for the past week. If any one has any ideas, besides restore, please let me know. You would save me a lot of extra work.
There is an online form which we need to fill out for each of our inventory units. This form has several dozen fields though. We have to fill each one out for every unit that we ship (which is about a hundred a day).
But the good news is, about 25 of these 30 fields are always filled in with the same value (our name, address, city, state, zip, insurance, insurance no, carrier, carrier no, etc).
What we want is a way to fill out these fields for us automatically, and all we have to do is type in the remaining 5 fields ourselves.
My temporary solution was to create a form with 5 fields and a Submit button. Then using AppActivate "Inventory Adjustment - Microsoft Internet Explorer", we would use the SendKeys function and virtually "type" in the 5 variable values and the 25 other "hardcoded" values, using the Tab key to move in between fields.
However, we have encountered some issues using SendKeys. For one, the cursor doesn't always start in the correct place, and the fields become horribly offset and skewed. Also we notice that sometimes the cursor moves "too fast" apparently and skips pasting some of the values (an issue we tried to resolve by using "wait" commands, but it didn't help consistently enough).
Is there a better way to use Visual Basic to fill in fields in an Internet Explorer window? :(
This is straight out of the Access help files...
"Create a Visual Basic procedure"
You can set an event property (event property: A named attribute of a control, form, report, data access page, or section you use to respond to an associated event. You can run a procedure or macro when an event occurs by setting the related event property.) for a form, report, or control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) to [Event Procedure] to run code in response to an event. Microsoft Access creates the event procedure (event procedure: A procedure that is automatically executed in response to an event initiated by the user or program code, or that is triggered by the system.) template for you. You can then add the code you want to run in response to the particular event.
Open a form or report in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
Display the property sheet for the form or report, or for a section or control on the form or report.
Click the Event tab.
Click the event property for the event that you want to trigger the procedure. For example, to display the event procedure for the Change event, click the OnChange property.
Click Build "..." next to the property box to display the Choose Builder dialog box.
Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module (form module: A module that includes code for all event procedures triggered by events occurring on a specific form or its controls.) or report module (report module: A module that includes code for all event procedures triggered by events occurring on a specific report or its controls.). These statements define, or declare, the event procedure.
Microsoft Access automatically declares event procedures for each object and event in a form or report module by using the Private keyword to indicate that the procedure can be accessed only by other procedures in that module.
Add the code to the event procedure that you want to run when the event occurs. For example, to produce a sound through the computer's speaker when data in the CompanyName text box changes, add a Beep statement to the CompanyName_Change event procedure, as follows:
Private Sub CompanyName_Change()
*The event procedure runs each time the Change event occurs for the object.
Has anyone experienced this problem?
Ver: MS Access 2002 (10.6501.6735) SP3
MS Access / MS Visual Basic will not step through the code after I set breakpoints for debug.
This has always worked but recently it has stopped?
Anyone have any ideas? maybe a hotkey to toggle debug on/off?
I have an access Query(named newSerial) :
SELECT TOP 1 (Productbase.Serialnumber+1) AS Expr1 FROM ProductBase
ORDER BY (Productbase.Serialnumber+1) DESC;
I want to access this new value. Its not a key since an autonumber may be any number. When I create a new product. How do I get this value inside a form?
It works fine when clicking on it. But if I use an unbound box I get 1 as result and not like 76067 which it should be. If I click the query in access it works fine and show 76067.
' gives null
' which gives "Access cant find the field "|" refered to you in your expression"
What should I write? Whats the correct expression?
I am in the process of re-writing an Access system that was created with 100% macros. I need to convert the custom menus that were created with macros to vb code. I tried to convert the code to modules, but I can tell that is not the path. Does anyone have a simple example? For instance, the first custom menu is just File, Exit.
Thanks in advance.
I am hoping someone might be able to help me out. I am trying to create a delete query in VB for access.
I have managed to get it to run the delete query, but I would like to program in the responses to the following questions:
"You are about to run a delete query that will modify data in your table. Are you sure.... Yes"
"You are about to delete X row(s) from the specified table. Yes"
Here is what I have so far:
Private Sub Command32_Click()
DoCmd.OpenQuery "DELETEQUERY", acViewNormal, acEdit
Does anyone know how to do this???
Trying hard to create new database. After editing VB code error message appears
"error accessing file, network connection may have been lost" tables and forms are set
in one file. This has occured numerous times. If I had hair I would be pulling it out .
It seems to lose the code for a form. If you delete the form it remains in the Project Browser.
There seems to be a "remove icon" but it is deactivated.
Any ideas...Im desperately running short of time, any help will be gratefully accepted
I am developing a database where I need to print various forms/reports but I need one report to go to a label printer and another to an A5 printer and another to an A4 colour printer.
All I know is the "DoCmd.PrintOut" method but I don't know how to specify which device ?
Anyone have any ideas ?
I am not new to Access, however I am just starting to get to the point where I need visual basic to complete some things I want to try. My question is, how do I code a button (or modify an existing one) in my form that will email the same output I get when I use a Print Report button. Here is what I have from my standard buttons:
Private Sub Save_Svc_Record_Click()
On Error GoTo Err_Save_Svc_Record_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Private Sub Prt_Svc_Record_Click()
On Error GoTo Err_Prt_Svc_Record_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
Private Sub Add_Svc_Record_Click()
On Error GoTo Err_Add_Svc_Record_Click
DoCmd.GoToRecord , , acNewRec
Thanks in advance for any help I can get.
What does visual basic have that VBA does not?
I was working in my database today and I received an error message that the Visual Basic Application file is corrupt. I can not do anything with the database because the error message comes up and I can not get past it.
I was wondering if there is an easy way to convert an Access db into a Visual Basic application?
My end goal is to learn Visual Basic to be able to produce programs rather than access databases that way I would not have to worry about what version of access they have and so on. If someone has some tips on this please let me know.
I have a form which I am using to search a table. It has several input fields and it then searches the table on the users input and displays the results in a new form. Here is the basic code which accompishes this:
If [Forms]![Search - Wigan]![Inv No] <> "" Or [Forms]![Search - Wigan]![Descrip] <> "" Or ... Then DoCmd.OpenForm "Search Results", , , "[Wigan Goods Log]![Inventory No]=[Forms]![Search - Wigan]![Inv No]" & _
"or[Wigan Goods Log]![Description]=[Forms]![Search - Wigan]![Descrip]" & _
"or[Wigan Goods Log]![Goods In No]=[Forms]![Search - Wigan]![Goods In No]" & _
"or[Wigan Goods Log]![Make]=[Forms]![Search - Wigan]![Make]" & _
"or[Wigan Goods Log]![Date Received]=[Forms]![Search - Wigan]![Date Rcvd]" & _
"or[Wigan Goods Log]![Model No]=[Forms]![Search - Wigan]![Mod No]" & _
"or[Wigan Goods Log]![Part No]=[Forms]![Search - Wigan]![Part No]", acFormEdit, acWindowNormal
DoCmd.RunMacro "No Search Criteria"
The if statement initially checks to make sure at least one field has been filled in to prevent searching for nothing. The openform command uses a filter to open only records which have values the same as those input by the user.
The problem is that if you enter criteria which doesn't match anything in the database the search displays one blank record, whereas I would prefer if I could somehow put another statement which would only open the results form if at least one reults was found, i.e. if inv no is in table or if descrip is in table... then open display results.
Unfortunately I have no idea how i can achieve this. Please help
I was working in my database and received an error message " the visual basic application is corrupt in this file..." I can not use my database nor can I get past this error message. Best I can tell all data is still there, how do I get to it?
I want an append query to add records to one of several tables, depending upon the value of a variable entered into a form. This variable is also used to select the appropriate records. The query is invoked from the visual basic code behind the form.
Can anyone tell me how to edit the destination table from within the visual basic?
I am an access novice so was wondering in anyone can help me.
Can anyone post the code I need to enter to do the following;
1) Import a file from my local hard drive into a designated table in access
2) Export a number of tables back onto my local hard drive
I would like it so these actions occur when I click on the relevent command box on my form
Any help would be very much appreciated
Anyone have any idea how or why Visual Basic would automatically round up a calculation. I have a formula that simply grabs the rate from a table and multiplies it by two. However, for some reason, it grabs the rate and then rounds it up to the nearest whole number. I have no Int or Fix functions at work, so I'm really confused at this point. Is there any way to stop this? Thanks =)
How would i put in a html link in vb code
I want an e-mail to be sent to a user with a link back to the microsoft access database
anyone know how to do this?
I want to start using VBA with my Access applications but am at a loss as how to start. I really appreciate all the help I've received from the people on this forum but I feel it's time I jump into the VB phase. But it's been years since I've done any programming. Also, it appears there are different versions of visual basic so I'm a bit confused here as well.
Is there any good reference material or books anyone could suggest or other ways to get started? And if someone could set me straight as to which VB is used in Access I'd appreciate that as well. I don't want to waste time going after something that doesn't apply. And maybe there is only one so help me out here.
I am intermediate developer or maybe a basic developer of Acces Databases. More or well, i can develop databases in pure access and little bit knowledge of Visual Basic programming for Access. I want to develop my programming skills of access databases with Visual basic. I want to learn programming Modules, procedures, functions etc.
So, could any of you suggest me better online sources where I could learn visual basic for access from basics till advanced levels....Any good books??
Thanking you all in advance..
Just had a general question about either SQL or Visual Basic resources. I'm new to designing databases and have basically had to combine what I knew of Access before and things that I've learned throughout the process of the project I'm working on. As I've learned more (a lot of which I've picked up from this forum) I've also become a little familiar with the basics of code through SQL statements and Visual Basic. My first question is if, in your opinion, it's possible to learn a decent amount about SQL/VBA from a book or if it requires more extensive training (i.e. taking classes). Second, if you do feel it's reasonable to learn a good deal about these topics from a book, if anyone had recommendations on any one book to get me started that contained both going over the basic aspects and also taking a further look into more intermediate elements. Thanks in advance for your help.
As part of the macro, I have the Set Warnings set to No. If I start the Macro from the Property Event drop down, the message still appears. If I start the macro from the Visual Basic Module using the DoCmd.RunMacro option, the warning messages do not appear. Part of the Macro switches windows to Excel. As the last step of the Macro, I try to open a form I have previously closed. When the windows focus goes back to Excel, I only see a blank screen if I start the macro from the VB, yet if I start the Macro from the Property drop down menu, the form opens properly. Is there some way around this to get the best of both worlds?
Not quite sure if this is the right forum, but my system requires a button that opens the A drive. I know it would be much simpler to go through the File>Save as process, but my users are difficult
I was told I would need to create a macro first, and then edit it in visual basic so that it opened the A drive window.
Does anyone have any ideas about how I would do this?
Any help greatly appreciated
My form has an errro in and I can't seem to find it anywhere. It works good, but every so often I get an error that says "Error accessing file, network connection may have been lost", then I click ok and the debugger screen comes up, grayed out. Then sometimes the form get stuck and starts messages saying there's a error/
I have a form which I use for many different tasks, in order to save database size, reuse code and keep things at their simplest.
Sometimes, I'll click a button to open the form which will only show specific filtered data in "Form" view, using a default "Dialog" border style.
Sometimes, I click another button which opens the same form, filters different data and displays the form in "DataSheet" view, but this time, I want the borderstyle to be "Sizable".
What I need is a line of VB code which will force the form to open-up with "Sizable" borderstyle, regardless of the form's Design view settings.
Something that would look like this (which obviously doesn't work):
Forms!frmFormName.Borderstyle = sizable
Does anyone have suggestions?
I would like to know what name I need to give to a sub procedure so that it runs when a database is opened.
Basically, I want it to do the same thing an autoexec macro would do except I would prefer to do it in VBA if possible as I prefer not to have macros if possible.
Any help would be appreciated.
I have an Access Project application that was originally referencing VB6.olb. This application was packaged to distribute the vb6.olb object library. The application appeared to install fine and work fine. But, now after the application is uninstalled, the next time Access 2003 is started it goes through a self-repair.
It appears that this self-repair is caused by the removal of vb6.olb during the un-install of the application. I think this object library is included in the VBA DLLs and probably should not have been included in the original install.
Does anyone have any insight as to why the uninstall of this application would cause Access to go through a self-repair? The self-repair only happens after you try to execute VBA code.