We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

easy questions about access forms

luchko
luchko asked
on
Medium Priority
179 Views
Last Modified: 2010-05-18
Hi to everyone!

Here are some easy points for whoever answers me those 2 easy questions:

1.) I have set up a form and I have a column in my database which is called "Searchword" (let's say it's column #3). I would like to have a field in my form in which I would type the searchword and the other fields of the form would show me the whole information in the respective entry which I could later edit. How can I achieve that?

2.) I also have a field in this form (and a column in my database) where I manually typed the date of the last change in this entry. I would really like this to happen automatically, that is, I would enter a change in any field of my form and the date of change (shown both in the database and the form) should be set automatically to the current date.

Thank you all in advance!

P.S.: Or should I have probably posted my question in the MS Office category?
Comment
Watch Question

Commented:
You should post a link to this in the Access forum - but while it's here:


2.  In the BeforeUpdate event on the form add something like this (assuming the date box on the screen is called txtDateUpdated)

Me.txtDateUpdated = Date()

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Ok, I posted the link.

However, I tried your proposal, and it didn't work. Can it be that I somehow set some wrong properties of the date box?!
CERTIFIED EXPERT
Commented:
1) The easiest way is via an extra field on your form not bound to any data in your table, let's call it txtSearch. In the after update event of txtSearchyou put in the code to navigate to the required record. E.g
   me.recordset.findfirst "SearchWord = '" & txtSearch & "'"
2) As in cquinn's suggestion, use the BeforeUpdate evnt of the form to assign the change date. If you don't want to show the DateUpdated on the form itself you can assign the value directly to the underlying field:
   me!DateUpdate = Now()

I've used Now() rather than date to include the time of the change as well as the date

Commented:
Have you tried the Combo box Wizard to find a record on your Form?
1) Create a Combo box using the Wizard
2) Choose the third option - "Find a record on my form based on the value selected in my Combo box"
3) Coose the field, which will be "Searchword"

you then have the option of typing in the word or searching for it via the drop down list
CERTIFIED EXPERT

Commented:
if isnull(me.DateUpdate) then
    me.DateUpdate = date()    ----- or Now()
end if


This will prevent this date field from being OVERWRITTEN everytime you view it.

(There are other ways to achieve this result)

Scott C

Author

Commented:
cquinn, I modified your answer a little bit and it did the job. As soon as my first question is answered, you would become half the points.
However, no one provided a satisfying answer to my first question. smidgen, yours does not work for me at all and MikeToole's also returns an error (which is quite logical). Perhaps I did not explain the situation that good, so here it is once again:
I have a form which shows the record sets and I can scroll through them. I also have a field and a "Search" button next to it. What I would like to have is, as soon as I press the "Search" button, the word in the field is being searched for in, say, the third column in my database, and, once found, the form would show me all the data in it. How do I do that?
Please ask if further explanation is needed.

Author

Commented:
Ok, MikeToole, I got it :) Say, is there any possibility that Access would search only the first letters of the searchword instead of the whole of it? Like, say, if my searchword is "appleseed", and I type "apples", then the word "appleseed" would be searched for?

Author

Commented:
OK, still no answer today, so I just split the points. I hope that at least someone would try to answer my last question. Thanks to everyone for their posts!
CERTIFIED EXPERT

Commented:
Yes:

me.recordset.findfirst "SearchWord Like '" & txtSearch & "*'"

for your example this would come out as:

me.recordset.findfirst "SearchWord Like 'Apples*' "

The * at the end of the search term is a wild card character, saying that you don't care whar comes after.

If you put Like '*apples*'  it would find the first record containing the string "apples" anywhere in the SearchWord field.

The single apostrophe '  either side of the search term is needed because searchword is a text field


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.