Hi.
I'm very new to MS Access and have been presented with an Access database
of contacts by my employer.
I am trying to redesign the main form of the database so that a button
entitled 'search' may be clicked on by the user and the user can then
search all records by postcode. I want to do this to prevent duplicate
data entry.
I have searched the helpfiles and various forums but can't make head nor
tail of the basics. All the articles I look at are far too advanced and
I'm sure I'm just not getting the terminology right. Do I need to
'record a macro' or set up an event? I have the button on the form, but
nothing behind the button. I have all the data in tables behind the
form, but no 'query' (?) with which the user can search.
Any help gratefully received.
Thank you,
Steph. 8 3184
Steph wrote: Hi.
I'm very new to MS Access and have been presented with an Access database of contacts by my employer.
I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
I have searched the helpfiles and various forums but can't make head nor tail of the basics. All the articles I look at are far too advanced and I'm sure I'm just not getting the terminology right. Do I need to 'record a macro' or set up an event? I have the button on the form, but nothing behind the button. I have all the data in tables behind the form, but no 'query' (?) with which the user can search.
Any help gratefully received.
Thank you,
Steph.
What do you mean by searching? Is the main form a single record form
and you want to present a form a list of records to search? Or is your
main form a continuous/datasheet form and you want to filter the
records? Do you want the main form to filter to records with that post
(postal code?) or to a specific record?
You could do something as simple as using an inputbox to get the post
code. Ex:
Private Sub GetPostCode
Dim strPostCode As String
strPostCode = InputBox("What post code are you searching for?",
"Post Code Search")
If strPostCode > "" Then
Me.filter = "PostCode = "" & strPostCode & ""
Me.FilterOn = True
End If
If you could provide a brief description with some more specifics, you
may get a better response.
Salad <oi*@vinegar.com> wrote in
news:LE****************@newsread3.news.pas.earthli nk.net: Steph wrote: Hi.
I'm very new to MS Access and have been presented with an Access database of contacts by my employer.
I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
I have searched the helpfiles and various forums but can't make head nor tail of the basics. All the articles I look at are far too advanced and I'm sure I'm just not getting the terminology right. Do I need to 'record a macro' or set up an event? I have the button on the form, but nothing behind the button. I have all the data in tables behind the form, but no 'query' (?) with which the user can search.
Any help gratefully received.
Thank you,
Steph. What do you mean by searching? Is the main form a single record form and you want to present a form a list of records to search? Or is your main form a continuous/datasheet form and you want to filter the records? Do you want the main form to filter to records with that post (postal code?) or to a specific record?
Er... I am guessing it's the latter. There are 900 business records in
this database. Company name, address phone number etc. The front end
screen (form?) is just a series of user buttons and, for the user, I
don't want it to get more complicated than that. I know that I can look
up a postcode if I click into the postcode field on the database, and
then click on the binoculars - but I need a user to be able to do that
without a) accessing the raw database and working only from the form and
b) taxing their brain too heavily.
You could do something as simple as using an inputbox to get the post code. Ex: Private Sub GetPostCode Dim strPostCode As String strPostCode = InputBox("What post code are you searching for?", "Post Code Search") If strPostCode > "" Then Me.filter = "PostCode = "" & strPostCode & "" Me.FilterOn = True End If
If you could provide a brief description with some more specifics, you may get a better response.
I don't mean to be rude, but the above may as well be in Japanese for all
the sense I can make of it.
I have in front of me a "form" - an MS Access input screen which has a
database behind it - this form contains buttons I can click on. All of
which currently perform an action, except the 'search' button. This is
the form already created by my employer. What I am trying to do is
improve upon it and offer a way users can search under postcode (and
perhaps other criteria too) so that when they are entering the details of
a potentially new company, into the database, they can rule out the
possibility of a duplicate entry beforehand.
I don't need it to generate a report per se, just say "no records with
that, or part of that, postcode were found"
-- http://www.tosaveme.blogspot.com
Puck wrote: Salad <oi*@vinegar.com> wrote in news:LE****************@newsread3.news.pas.earthli nk.net:
Steph wrote:
Hi.
I'm very new to MS Access and have been presented with an Access database of contacts by my employer.
I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
I have searched the helpfiles and various forums but can't make head nor tail of the basics. All the articles I look at are far too advanced and I'm sure I'm just not getting the terminology right. Do I need to 'record a macro' or set up an event? I have the button on the form, but nothing behind the button. I have all the data in tables behind the form, but no 'query' (?) with which the user can search.
Any help gratefully received.
Thank you,
Steph.
What do you mean by searching? Is the main form a single record form and you want to present a form a list of records to search? Or is your main form a continuous/datasheet form and you want to filter the records? Do you want the main form to filter to records with that post (postal code?) or to a specific record?
Er... I am guessing it's the latter. There are 900 business records in this database. Company name, address phone number etc. The front end screen (form?) is just a series of user buttons and, for the user, I don't want it to get more complicated than that. I know that I can look up a postcode if I click into the postcode field on the database, and then click on the binoculars - but I need a user to be able to do that without a) accessing the raw database and working only from the form and b) taxing their brain too heavily.
You could do something as simple as using an inputbox to get the post code. Ex: Private Sub GetPostCode Dim strPostCode As String strPostCode = InputBox("What post code are you searching for?", "Post Code Search") If strPostCode > "" Then Me.filter = "PostCode = "" & strPostCode & "" Me.FilterOn = True End If
If you could provide a brief description with some more specifics, you may get a better response.
I don't mean to be rude, but the above may as well be in Japanese for all the sense I can make of it.
I have in front of me a "form" - an MS Access input screen which has a database behind it - this form contains buttons I can click on. All of which currently perform an action, except the 'search' button. This is the form already created by my employer. What I am trying to do is improve upon it and offer a way users can search under postcode (and perhaps other criteria too) so that when they are entering the details of a potentially new company, into the database, they can rule out the possibility of a duplicate entry beforehand.
I don't need it to generate a report per se, just say "no records with that, or part of that, postcode were found"
There are many ways to search for a something. I have created a generic
form for my app. It contains a form and subform. I pass to the form
which table I want to use. I then create a SQL statement, on the fly,
to display the fields for whichever table I am displaying. I then have
a combo box where the op selects the field to search on and a text field
to enter the value to search. It then displays all records matching
that criteria in the search field.
In another method, I present a continuos form. Above each label for
each column I present a textbox. When a person enters something in the
textbox, I then search for the first occurrance in that column that
matches the value entered by the op...I allow the first part of the
column or if the value is contained in any part of the field. Using the
F3 key, I can find the next occurrence.
It is very easy for me to update these search methods since I wrote
them, understand them, and can program. If you are new to this, the
search part may be a bit difficult, especially if you are new to
programming.
The first step you need to come up with is a method you think would be
easy for the user to use to find a record. Maybe a combo box is all you
need. For example, you could have a combo box that presents a list of
search fields. The fields might be Name, PostCode, City, State, etc.
The second combo lists all the records in the cust table. The rowsource
may be
Select ContactID, CustName, CustCity, CustPostCode From Customers Order
By CustName.
You could hid the ContactID by setting its with to 0.
In the AfterUpdate event of the first combo, change the SQL for the
second combo. Ex:
DIm strSQL As String
If Me.combo1 = "Name" Then
strSQL = " Select ContactID, CustName, CustCity, CustState,
CustPostCode From Customers Order By CustName"
elseIf Me.combo1 = "PostCode" Then
strSQL = " Select ContactID, CustPostCode, CustName, CustCity,
CustState From Customers Order By CustPostCode"
elseIf Me.combo1 = "City" Then
strSQL = " Select ContactID, CustCity, CustName, CustState,
CustPostCode From Customers Order By CustCity
ENdif
Me.Combo2.RowSOurce = strSQL
Now the person can start entering the value to search for and the second
combo will start getting closer and closer to the field being searched for.
You can then do what you want based on the search results.
It all depends on how deep you want to get into this. Good luck.
Salad <oi*@vinegar.com> wrote in
news:u_******************@newsread2.news.pas.earth link.net: Puck wrote:
Salad <oi*@vinegar.com> wrote in news:LE****************@newsread3.news.pas.earthli nk.net:
Steph wrote:
Hi.
I'm very new to MS Access and have been presented with an Access database of contacts by my employer.
I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
I have searched the helpfiles and various forums but can't make head nor tail of the basics. All the articles I look at are far too advanced and I'm sure I'm just not getting the terminology right. Do I need to 'record a macro' or set up an event? I have the button on the form, but nothing behind the button. I have all the data in tables behind the form, but no 'query' (?) with which the user can search.
Any help gratefully received.
Thank you,
Steph.
What do you mean by searching? Is the main form a single record form and you want to present a form a list of records to search? Or is your main form a continuous/datasheet form and you want to filter the records? Do you want the main form to filter to records with that post (postal code?) or to a specific record?
Er... I am guessing it's the latter. There are 900 business records in this database. Company name, address phone number etc. The front end screen (form?) is just a series of user buttons and, for the user, I don't want it to get more complicated than that. I know that I can look up a postcode if I click into the postcode field on the database, and then click on the binoculars - but I need a user to be able to do that without a) accessing the raw database and working only from the form and b) taxing their brain too heavily.
You could do something as simple as using an inputbox to get the post code. Ex: Private Sub GetPostCode Dim strPostCode As String strPostCode = InputBox("What post code are you searching for?", "Post Code Search") If strPostCode > "" Then Me.filter = "PostCode = "" & strPostCode & "" Me.FilterOn = True End If
If you could provide a brief description with some more specifics, you may get a better response.
I don't mean to be rude, but the above may as well be in Japanese for all the sense I can make of it.
I have in front of me a "form" - an MS Access input screen which has a database behind it - this form contains buttons I can click on. All of which currently perform an action, except the 'search' button. This is the form already created by my employer. What I am trying to do is improve upon it and offer a way users can search under postcode (and perhaps other criteria too) so that when they are entering the details of a potentially new company, into the database, they can rule out the possibility of a duplicate entry beforehand.
I don't need it to generate a report per se, just say "no records with that, or part of that, postcode were found"
There are many ways to search for a something. I have created a generic form for my app. It contains a form and subform. I pass to the form which table I want to use. I then create a SQL statement, on the fly, to display the fields for whichever table I am displaying. I then have a combo box where the op selects the field to search on and a text field to enter the value to search. It then displays all records matching that criteria in the search field.
In another method, I present a continuos form. Above each label for each column I present a textbox. When a person enters something in the textbox, I then search for the first occurrance in that column that matches the value entered by the op...I allow the first part of the column or if the value is contained in any part of the field. Using the F3 key, I can find the next occurrence.
It is very easy for me to update these search methods since I wrote them, understand them, and can program. If you are new to this, the search part may be a bit difficult, especially if you are new to programming.
The first step you need to come up with is a method you think would be easy for the user to use to find a record. Maybe a combo box is all you need. For example, you could have a combo box that presents a list of search fields. The fields might be Name, PostCode, City, State, etc. The second combo lists all the records in the cust table. The rowsource may be
Select ContactID, CustName, CustCity, CustPostCode From Customers Order By CustName.
You could hid the ContactID by setting its with to 0.
In the AfterUpdate event of the first combo, change the SQL for the second combo. Ex: DIm strSQL As String If Me.combo1 = "Name" Then strSQL = " Select ContactID, CustName, CustCity, CustState, CustPostCode From Customers Order By CustName" elseIf Me.combo1 = "PostCode" Then strSQL = " Select ContactID, CustPostCode, CustName, CustCity, CustState From Customers Order By CustPostCode" elseIf Me.combo1 = "City" Then strSQL = " Select ContactID, CustCity, CustName, CustState, CustPostCode From Customers Order By CustCity ENdif Me.Combo2.RowSOurce = strSQL
Now the person can start entering the value to search for and the second combo will start getting closer and closer to the field being searched for.
You can then do what you want based on the search results.
It all depends on how deep you want to get into this. Good luck.
Thanks for trying, Salad. It's still going right over my head and
bouncing off the walls behind me.
I just wondered if there was a standard event procedure, or macro (I
still don't understand the difference, despite flogging the sodding
helpfile all night) for data look-up. Since it's an integral part of
working with any data input interface, I expected it to be a standard
procedure. Clearly it isn't and I'm not a programmer. They don't pay me
enough to be arsed with all that.
No harm in trying though.
Thanks again.
Steph wrote: Salad <oi*@vinegar.com> wrote in news:u_******************@newsread2.news.pas.earth link.net:
Puck wrote:
Salad <oi*@vinegar.com> wrote in news:LE****************@newsread3.news.pas.eart hlink.net: Steph wrote:
>Hi. > >I'm very new to MS Access and have been presented with an Access >database of contacts by my employer. > >I am trying to redesign the main form of the database so that a >button entitled 'search' may be clicked on by the user and the user >can then search all records by postcode. I want to do this to >prevent duplicate data entry. > >I have searched the helpfiles and various forums but can't make head >nor tail of the basics. All the articles I look at are far too >advanced and I'm sure I'm just not getting the terminology right. >Do I need to 'record a macro' or set up an event? I have the button >on the form, but nothing behind the button. I have all the data in >tables behind the form, but no 'query' (?) with which the user can >search. > >Any help gratefully received. > >Thank you, > >Steph.
What do you mean by searching? Is the main form a single record form and you want to present a form a list of records to search? Or is your main form a continuous/datasheet form and you want to filter the records? Do you want the main form to filter to records with that post (postal code?) or to a specific record?
Er... I am guessing it's the latter. There are 900 business records in this database. Company name, address phone number etc. The front end screen (form?) is just a series of user buttons and, for the user, I don't want it to get more complicated than that. I know that I can look up a postcode if I click into the postcode field on the database, and then click on the binoculars - but I need a user to be able to do that without a) accessing the raw database and working only from the form and b) taxing their brain too heavily. You could do something as simple as using an inputbox to get the post code. Ex: Private Sub GetPostCode Dim strPostCode As String strPostCode = InputBox("What post code are you searching for?", "Post Code Search") If strPostCode > "" Then Me.filter = "PostCode = "" & strPostCode & "" Me.FilterOn = True End If
If you could provide a brief description with some more specifics, you may get a better response.
I don't mean to be rude, but the above may as well be in Japanese for all the sense I can make of it.
I have in front of me a "form" - an MS Access input screen which has a database behind it - this form contains buttons I can click on. All of which currently perform an action, except the 'search' button. This is the form already created by my employer. What I am trying to do is improve upon it and offer a way users can search under postcode (and perhaps other criteria too) so that when they are entering the details of a potentially new company, into the database, they can rule out the possibility of a duplicate entry beforehand.
I don't need it to generate a report per se, just say "no records with that, or part of that, postcode were found" There are many ways to search for a something. I have created a generic form for my app. It contains a form and subform. I pass to the form which table I want to use. I then create a SQL statement, on the fly, to display the fields for whichever table I am displaying. I then have a combo box where the op selects the field to search on and a text field to enter the value to search. It then displays all records matching that criteria in the search field.
In another method, I present a continuos form. Above each label for each column I present a textbox. When a person enters something in the textbox, I then search for the first occurrance in that column that matches the value entered by the op...I allow the first part of the column or if the value is contained in any part of the field. Using the F3 key, I can find the next occurrence.
It is very easy for me to update these search methods since I wrote them, understand them, and can program. If you are new to this, the search part may be a bit difficult, especially if you are new to programming.
The first step you need to come up with is a method you think would be easy for the user to use to find a record. Maybe a combo box is all you need. For example, you could have a combo box that presents a list of search fields. The fields might be Name, PostCode, City, State, etc. The second combo lists all the records in the cust table. The rowsource may be
Select ContactID, CustName, CustCity, CustPostCode From Customers Order By CustName.
You could hid the ContactID by setting its with to 0.
In the AfterUpdate event of the first combo, change the SQL for the second combo. Ex: DIm strSQL As String If Me.combo1 = "Name" Then strSQL = " Select ContactID, CustName, CustCity, CustState, CustPostCode From Customers Order By CustName" elseIf Me.combo1 = "PostCode" Then strSQL = " Select ContactID, CustPostCode, CustName, CustCity, CustState From Customers Order By CustPostCode" elseIf Me.combo1 = "City" Then strSQL = " Select ContactID, CustCity, CustName, CustState, CustPostCode From Customers Order By CustCity ENdif Me.Combo2.RowSOurce = strSQL
Now the person can start entering the value to search for and the second combo will start getting closer and closer to the field being searched for.
You can then do what you want based on the search results.
It all depends on how deep you want to get into this. Good luck.
Thanks for trying, Salad. It's still going right over my head and bouncing off the walls behind me.
I just wondered if there was a standard event procedure,
No. The Combobox, if the form is bound, will present 3 options...the
third being to find a record based on value entered.
or macro (I still don't understand the difference, despite flogging the sodding helpfile all night) for data look-up. Since it's an integral part of working with any data input interface, I expected it to be a standard procedure.
No. Unfortunately, there are many ways to search for a record.
Clearly it isn't and I'm not a programmer. They don't pay me enough to be arsed with all that.
I know what you mean. I'm not sure if there's something in NorthWind
that will help you in finding a record but you may want to look at it.
Open up Northwind and check it out. Also, when you open up Access, you
can create various databases (click on Database Wizard...in fact there
is a contact manager as one of the databases). Maybe one of them has a
form with search capabilities.
No harm in trying though.
Thanks again.
Good luck.
"Steph" <st*****@hotmail.com> wrote in message
news:Xn******************@130.88.202.229... Hi.
I'm very new to MS Access and have been presented with an Access database of contacts by my employer.
I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
This ought to be fairly straightforward. Is the post code stored in its own
field or is it part of another?
Regards,
Keith. www.keithwilby.com
"Keith" <ke*********@baeAWAYWITHITsystems.com> wrote in
news:42**********@glkas0286.greenlnk.net: "Steph" <st*****@hotmail.com> wrote in message news:Xn******************@130.88.202.229... Hi.
I'm very new to MS Access and have been presented with an Access database of contacts by my employer.
I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
This ought to be fairly straightforward. Is the post code stored in its own field or is it part of another?
It's in the post code field.
"Steph" <st*****@hotmail.com> wrote in message
news:Xn******************@130.88.202.229... "Keith" <ke*********@baeAWAYWITHITsystems.com> wrote in news:42**********@glkas0286.greenlnk.net:
"Steph" <st*****@hotmail.com> wrote in message news:Xn******************@130.88.202.229... Hi.
I'm very new to MS Access and have been presented with an Access database of contacts by my employer.
I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
This ought to be fairly straightforward. Is the post code stored in its own field or is it part of another?
It's in the post code field.
So you want to return all records containing a certain string in a text box?
Have you considered using the built-in "Filter by Form" feature? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Si |
last post by:
Hi All.
This may be DB issue, but rather that cross post, I'll post here first.
I have a page on a site that submits a form to search for various
different values in a database. It's a...
|
by: David Van D |
last post by:
Hi there,
A few weeks until I begin my journey towards a degree in Computer
Science at Canterbury University in New Zealand,
Anyway the course tutors are going to be teaching us JAVA wth bluej...
|
by: ZafT |
last post by:
Thanks for any help in advance. I am using this exact same piece of code on
a server running PHP4, but for some reason when I run it on dreamhost using
PHP5, the search does not register and it...
|
by: PEB |
last post by:
POSTING GUIDELINES
Please follow these guidelines when posting questions
Post your question in a relevant forum
Do NOT PM questions to individual experts - This is not fair on them and...
|
by: jarice1978 |
last post by:
Hello,
I have been scanning the internet for a few days now. That is not working. So now it is time to post!
I have read a few other posts on here about authentication but they do not match...
|
by: davecph |
last post by:
I'm constructing a website with a layout created with div-tags. They have a fixed width, float left, and display inline. When one of the div's contain a select-element the right-most div floats down...
|
by: iahamed |
last post by:
Hi Everyone,
I got two parts of my advance search to work, I am running out of Logic to
connect the third. My mind is in swing! Pleaseeeeeeeee Help me.
I have 3 Fiels to search, the First two...
|
by: gnawz |
last post by:
I have a pagination function I am using in a file called functions.php as below<? //Pagination functions function getPagingQuery($sql, $itemPerPage = 10) { if (isset($_GET) && (int)$_GET > 0) ...
|
by: jkershner |
last post by:
I have a js script inside php code that is connected to mysql, the php code is listing the results from the query then, the onmouseover function is supposed to show the description of the title for...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |