Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

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
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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Visual Basic
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 Replies !   View Related
Access To Visual Basic
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 Replies !   View Related
Microsoft Visual Basic
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 Replies !   View Related
Access And Visual Basic 6
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 Replies !   View Related
Visual Basic Error
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 Replies !   View Related
Using Visual Basic Together With Internet Explorer
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 Replies !   View Related
Create A Visual Basic Procedure
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()


End Sub

*The event procedure runs each time the Change event occurs for the object.

View Replies !   View Related
MS Visual Basic Debug Stepping ...HELP!
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 Replies !   View Related
Using Access Query In Visual Basic?
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 Replies !   View Related
Menus - Visual Basic Style
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 Replies !   View Related
Visual Basic Delete Query
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 Replies !   View Related
Visual Basic Error Message
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 Replies !   View Related
Printing To Different Devices In Visual Basic
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 Replies !   View Related
New To Visual Basic Coding In Access
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 Replies !   View Related
Edit Destination Table In Visual Basic
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 Replies !   View Related
Open File Code In 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

View Replies !   View Related
Automatic Rounding In Visual Basic Calculations?

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 Replies !   View Related
Setting Form Border Style Using Visual Basic
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 Replies !   View Related
Problem With Calling A Visual Basic Function From A Form Field
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 Replies !   View Related

Copyright 2005-08, All rights reserved