How to find a record?

[belfagor71]

Hi everyone ,

I have developped a table and some forms reated to that.
Since the main survey is quite long, I developed 4 forms to enter the data. Now I have a question related to data entry.
Suppose I start entering data for one record and I decide not to fill up all the 4 forms, I simply decide to go back to it later. How can I go to the same record and keep entering the data from where I left?

If I am in Datasheet view, I can easily use the Find option to look for the record. However, I dont seem to be able to understand how I can open the form of that record to enter the remaining data. I hope this makes sense!

Looking forward to hearing from you!!

Many thanks,
belfi

 

[plog]

What information do you have about the record you want to find? You would build another form to accomodate using that information as search criteria.

If you are assigning an autonumber primary key to every record and know that number, you would build a form with an input and a button. Enter the number in the input, click the button and it would directly open your survey form to the correct record.

If you know the last name of the survey taker you could have a drop down which lists all last names and then a button next to it. Select a last name, click the button and down below a subform would filter to show just records with that chosen last name. Next to each last name would be a button, the first name and any other information so that you could selct the correct one. You click a button next to the right record and are taken to the survey form.

The last method (search with subform) could be done with multiple fields. In all cases when using another form the key code behind that button that takes the user to the survey form is the DoCmd.OpenForm (http://msdn.microsoft.com/en-us/library/office/ff820845(v=office.15).aspx). You would use it to open the survey form to that exact record by using the primary key for each survey.

 

[belfagor71]

What information do you have about the record you want to find? You would build another form to accomodate using that information as search criteria.

Easy, I have the unique ID.

you would build a form with an input and a button. Enter the number in the input, click the button and it would directly open your survey form to the correct record.

Wha do you mean by this?
should I create another form? if so...how should I tell access how X record? Could you please give me mor edetails?
Many thanks!

 

[plog]

For ease of learning I would get this working with a new form. After that you could try and move it to the same form you are using, but for now, create a seperate form.

On that new form, in design view, put a text box and a button. Name the button something like "SearchID". Then go to the button and click the elipses (...) next to the On Click event and select 'Code Builder'. In that section you will put a DoCmd.Open form call. See the link I provided in the previous post for how that code should be set up.

 

[belfagor71]

Thanks a lot but I am unfortunately able to undertsand very little of what is written in that page you linked me...

Plus, I dont get any elipses on the button. If I right click on it, the menu gives me the option Build event.

 

[plog]

In design view of the form, right click on the button, go to Properties at the bottom of the dialog that pops up, then click on the Event tab and there will be an On Click event at the top with elipses to the right.

Here's an easier to understand link for DoCmd.OpenForm: http://www.techonthenet.com/access/forms/dialog.php

If that doesn't work for you, google using "DoCmd.OpenForm"

 

[belfagor71]

Many thanks, yes, I found out the elipses now
So basically I have to create a macro: but I have no clue on how to programme in VBA "Open the fomr X for the record ID XXX"

 

[vbaInet]

Thanks a lot but I am unfortunately able to undertsand very little of what is written in that page you linked me...

Sometimes these Microsoft pages are hard to understand

Here's an idea for the "remembering" part:
1. Create a table to store ID, Date/Time and UserID
2. Link this ID to your table (i.e. create a relationship)
3. Create a query that will pull down one or two fields from both tables, i.e. Date/Time, User ID and two others
4. Create a combo box on your form and use the query in step 3 as the Record Source and make sure to link this combo box to your form via the ID.
5. Create a "Bookmark Me" or "Remember Me" button that will UPDATE or INSERT data into the table in step 1.

So anything you hit Bookmark it will UPDATE or INSERT data into the table from step 1 with today's date/time, the ID, and the User ID and when you want to pull up a record you bookmarked, just select an item from the combo box.

Otherwise, I would go with plog's idea of creating a search form.

 

[plog]

but I have no clue on how to programme in VBA

I keep trying to lead you to the water of DoCmd.OpenForm, but you keep refusing to drink.

 

[belfagor71]

No, well I did not refuse to drink it just took me lot of time to understand and eventually make work it out
So I eventually managed to create a nice form which brings me back to the record I want to go to.
This is how it looks:




Now the big problem is: suppose I want to go back to the record 13. I use the button and the data for record 13 will automatically pop up. However what happens is that if I want to go to form 2, that form 2 is only linked to the 1st record of the database. So I can enter all the data that I want but if I want to enter data for the same record on the other forms, well, that won't work.

How can I fix this?

Many thanks!

 

[vbaInet]

When you open the other forms, filter it down to that record. You'll find this parameter in the OpenForm action:

DoCmd.OpenForm "[COLOR="red"]FormName[/COLOR]",,,"[COLOR="Red"]--Criteria goes here--[/COLOR]"

Here's a link that explains it all:
http://baldyweb.com/wherecondition.htm

 

[belfagor71]

When you open the other forms, filter it down to that record

What do you mean by filtering it down?

On the forms I did not put the 'Go to record' button...

 

[vbaInet]

Have you read the link?

 

[belfagor71]

I am reading the link...but takes me time to understand. So basically should I change the code I wrote for the 'Go to record button'? ah, no, wait, I should change the code for the button at the bottom of the page which says "Go to next section'. Correct? Now it will take me some time to figure it out...but will do. Hopefully!

 

[vbaInet]

You said...

However what happens is that if I want to go to form 2, that form 2 is only linked to the 1st record of the database.

So it appears that you are trying to open a second form (form 2) and navigate to a record that form 1 is already on using some sort of ID right? That's what the link explains.

 

[belfagor71]

So it appears that you are trying to open a second form (form 2) and navigate to a record that form 1 is already on using some sort of ID right?

Yes, basically, if I am on form 1 for the record No. 13 (Primary Key) if I save the record and then go to form 2, the data that I will be now entering on form 2 will be entered as belonging to the 1st record ever of the database, it will not be considered as part of the record number 13. So this is the issue I now have to sort.

That's what the link explains.

Yes, but it will take me time now to understand it, as I am very new to VBA

 

[vbaInet]

Take your time and come back with questions (if any).

 

[belfagor71]

Oh, well, here I am

So, this is the line of code I wrote
Private Sub name_Click()
DoCmd.OpenForm " cazzola_2 ", , , " ID = " & Me.ControlName & " ' "

End Sub

And the message that I get when I do the debug is "Compile Error: Method or data member not found"

 

[vbaInet]

Be careful with spaces when you're writing code. And also if you need to post code, it's nice to have it in code tags so that it's more legible. Here's how:
http://www.access-programmers.co.uk/forums/showthread.php?t=200247

It's throwing that error message because ControlName is not the name of the textbox that holds the ID.

 

[belfagor71]

It's throwing that error message because ControlName is not the name of the textbox that holds the ID.

mmm...name of the textbox? Is not ID a field only? so how can I know the name of the textbox holding ID?