Solved

Populate form based on a text box

Posted on 2007-11-28
7
580 Views
Last Modified: 2008-11-12
Hi Experts-

I have a form with in access which is pulling data from Ms Sql thru ODBC connection (a View). What i want is to have a text box as a search field where the user types in a number or part of a number and it will only display the relevant results in the form. I have done this before with a combo box but not with text box. What i have is right now when i open the form it pops up  a dialog saying enter parameter value (which is my text box used for searching) then i put in some number and it displays the info on the form. What i want to do is to be able to go directly to the form where i have the text box. i put in the number there and gives me the results and then i should be able to look up for another number. On my form record source i have the following query

SELECT * FROM myview WHERE Num=txtSearch.value or right(Num,5)=txtSearch.value; and the other text boxes where i see the results they all have their control source set to the respective fields(columns) of the view.  Any kind of help would be appreciated. Thanks
0
Comment
Question by:zafridi
7 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 40 total points
ID: 20367491
Just reset the RecordSource of your form when you need to search ... for example, if you have a Search button, and want to refer to a textbox:

Sub cmdSearch_Click()
  Me.Recordsource = "SELECT * FROM myview WHERE Num=" & txtSearch & " OR right(Num,5)=" & txtSearch.value
End Sub

This assumes Num is a numeric field; if not, then you'd need to surround it with single quotes.
0
 
LVL 6

Assisted Solution

by:messen1975
messen1975 earned 40 total points
ID: 20367497
Delete the Form Source from the Form Source box
In the properties for your Textbox create an After  Update Event for your textbox

Set the  After Update Event to

Me.Form.RecordSource = SELECT * FROM myview WHERE Num=txtSearch.value or right(Num,5)=txtSearch.value
Me.Form.Refresh


0
 
LVL 44

Accepted Solution

by:
GRayL earned 45 total points
ID: 20367551
If your form now provides the parameters pop-up box, all you need do is place the textbox on the form, and replace the parameters portion of the query behind the form with the textbox reference:
From:

Select * from myView where Num = [EnterNum];

to:

Select * from myView where Num = Forms!myFormName!txtSearch

Now when you open the form the textbox is empty so the form should be clear.  Now when you place a value in the textbox you want the form to re-query so add this code to the AfterUpdate event of the textbox:

Private Sub txtSearch_AfterUpdate()
  Me.requery
End Sub
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:zafridi
ID: 20369202
none of the above works for me...i guess u guys didn't quite get what i was looking for...simply put if i want to display values in a form(in text boxes) based on another text box that exists on the same form..how is it get done.. or i could have a subform that displays the results based on a textbox (exists on another form)..i think it should be pretty simple..i've done this before from a combo box but couldnt get it done from a text box..im not sure why. i hope i explained my question properly..thanks
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20369266
Do you now have a form, based on a parameter query which asks for a value for Num?  That's certainly what your question reads like.   On that same form you create a textbox named txtSearch, correct?.  You modified the query, and inserted the code? What happened when you ran my answer?
0
 

Author Comment

by:zafridi
ID: 20369440
This is what i have in the form record source now

SELECT * FROM dbo_EquipmentforAccessVw WHERE EquipSerialNum=Forms!frmSearchOptions!txtSearchVin;

and the text box after update event  Me.Requery

when i run it..the form opens but nothing shows...its just blank...not even the labels..no text nothing...thankss
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20369835
I see the problem.  An Access ODBC connection to a SQL table is processed in the SQL server.  It knows nothing about the local Access Form.  Somehow you have to get the value from the local form textbox control into something understood by the SQL parser - @varvalue ??
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question