Wildcard Search Using Visual Basic Problem

Sep 8, 2005

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
End If

If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & " (Searchtestdata.firstname) Like '*" & Me.txtfirstname & "*' AND"
End If

'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

End Sub

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

View Replies


Visual Basic

Feb 16, 2005

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!

Many thanks,

Robin :o

View 3 Replies View Related

Access To Visual Basic

Oct 12, 2005

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.

View 1 Replies View Related

Microsoft Visual Basic

Mar 18, 2008

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.

View 6 Replies View Related

Access And Visual Basic 6

Oct 17, 2004

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!!

View 1 Replies View Related

Visual Basic Error

Mar 24, 2005

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.

View 4 Replies View Related

Using Visual Basic Together With Internet Explorer

Aug 16, 2005

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? :(

View 2 Replies View Related

MS Visual Basic Debug Stepping ...HELP!

Jun 13, 2006

Hi all,

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?



View 3 Replies View Related

Using Access Query In Visual Basic?

Sep 18, 2006

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.
I tried:
' gives null
' which gives "Access cant find the field "|" refered to you in your expression"

What should I write? Whats the correct expression?

View 7 Replies View Related

Menus - Visual Basic Style

Sep 3, 2004

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.

View 1 Replies View Related

Visual Basic Delete Query

Oct 20, 2004

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
End Sub

Does anyone know how to do this???

View 2 Replies View Related

Visual Basic Error Message

Jan 13, 2005

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

View 1 Replies View Related

Printing To Different Devices In Visual Basic

Jan 31, 2005

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 ?


View 1 Replies View Related

New To Visual Basic Coding In Access

Mar 31, 2006

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

Exit Sub

MsgBox Err.Description
Resume Exit_Save_Svc_Record_Click

End Sub
Private Sub Prt_Svc_Record_Click()
On Error GoTo Err_Prt_Svc_Record_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection

Exit Sub

MsgBox Err.Description
Resume Exit_Prt_Svc_Record_Click

End Sub
Private Sub Add_Svc_Record_Click()
On Error GoTo Err_Add_Svc_Record_Click

DoCmd.GoToRecord , , acNewRec

Exit Sub

MsgBox Err.Description
Resume Exit_Add_Svc_Record_Click

End Sub

Thanks in advance for any help I can get.

View 1 Replies View Related

Edit Destination Table In Visual Basic

Aug 22, 2006

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?

In hope...

View 2 Replies View Related

Open File Code In Visual Basic

Jan 5, 2007


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

View 1 Replies View Related

Automatic Rounding In Visual Basic Calculations?

Mar 8, 2005


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 =)


View 4 Replies View Related

Setting Form Border Style Using Visual Basic

Mar 10, 2005

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?

View 1 Replies View Related

Queries :: Undefined Function In Query And Visual Basic

Apr 3, 2014

I'm doing some debugging over the phone.My friend has Access 2003.What does not work:When he codes the MID function in a query he gets an error like this:"Undefined function 'Mid' in expression."

In the Visual Basic editor, he gets the same error: MID is undefined.What DOES work:I walked him through the use of LEFT and RIGHT in the query and they work.In Excel, MID works.Might there be some odd ball Access configuration mess up, or maybe more likely, his Access installation is incomplete.

View 3 Replies View Related

Tables :: How To Rename Access Table In Visual Basic

Jul 20, 2013

i need to auto rename a table and for each new time to create date in table name and after that to use query into i am asking if that is possible.

my 1 table name is kalktbl and second table name is kalkulacija.

query is select * into kalkulacija from kalktbl and i am renaming kalkulacija.

i am using visual basic.

View 11 Replies View Related

How To Open Access 2007 From Visual Basic Program

Nov 22, 2014

I have a fully operational Visual Basic program that uses MS Office XP and it's related MS Access. Because of security concerns, I recently upgraded to MS Office 2007 and MS Access 2007. I can open MS Access 2007 using the shortcut on the desktop and run any of the database queries. When I try to run the Visual Basic program (called prjPathfinder) and open Access 2007, it says "Error 5: The type initializer for prjPathfinder.Upgrade Support threw an exception". It then shows the first form in prjPathfinder but does not open Access 2007. What am I missing?

View 3 Replies View Related

Problem With Calling A Visual Basic Function From A Form Field

May 30, 2005

Dear All.

I have a visual basic function, placed in a module.
I need to call it from a form field, and pass a value to it and the function has to return a value.

I tried it but it doesn't work
the value I get on the field is " #Name? "

any help will be very appreciated.


View 8 Replies View Related

General :: Rounding To Two Decimals Via Visual Basic (Microsoft Access)

Jun 12, 2014

In my database (access 2007) I have code similar to this

Dim Ulaz As String, Izlaz As String, UkKol As String, UkFin As String

and at the end of the code I have this

Me.NC = UkFin / UkKol

Me.NC respresents avaerage price.

If for result I have 0.498 value, I would like for that value to be shown as 0.50. In another words, it must be rounded to two places.

How can I do that in VB? That value will be passed to NC field on Form, however..

View 2 Replies View Related

Grabbing Time Values From Table In Visual Basic And Performing Calculations With Them

Nov 8, 2004


My boss wants me to program in visual basic, instead of using queries, a calculation between times, which values are gotten from a table. How do I perform a calculation between two fields from a table in code and what functions are there to calculate the amount of difference in minutes between times in visual basic? THANK YOU SO MUCH IN ADVANCE!


View 3 Replies View Related

Wildcard Search Funtionality

Jun 13, 2005

I am working on a form that will be used to search records in a table. I have created the query shown below:

WHERE (((tblSTC.STC) Like forms!frmsearch!txtsearch));

I want my user to be able to enter search criteria and allow the use of a wildcard. When i run the query no data is found??

Can anyone help, thanks.

View 3 Replies View Related

Queries :: Wildcard In Query Fed From A Search Form

Jan 9, 2014

I have built a search form to feed information to a query. The form uses combo boxes tied to table values, and all have wild cards built into them so if the user leaves the combo box they get all the records. I also have to text boxes representing start date and end date. I would like to allow the user to leave these blank and get all there as well. I have been looking through my one Access book, as well as searched all over the internet, but I cant seem to find the way to do this. My filter criteria for the text based combo boxes are like this:

Like "*" & [Forms]![ReportDesignF]![Company] & "*"

My filter criteria for the Start and End Dates looks like this:

Between [Forms]![ReportDesignF]![StartDate] And [Forms]![ReportDesignF]![EndDate]+#11:59:59 PM#

In this case of the user leaves the date values blank, the query returns nothing. I would like to return all dates if that is the case. I am assuming it is my lack of knowledge of wild cards and how they work with date values.

View 14 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved