go to a specifc record on another form

[jirrah]

hello
i have datasheet display on which double click event i added following

Private Sub Name_DblClick(Cancel As Integer)
Dim frmMyForm As Form
Dim stDocName As String
Dim rs As Object, strCriteria As String
stDocName = "frmEnterRecipes"
strCriteria = "[Name]=" & "'" & Me![Name] & "'"
DoCmd.OpenForm stDocName
Set rs = Forms![frmEnterRecipes].Recordset.Clone
rs.FindFirst strCriteria
Forms![frmEnterRecipes].Bookmark = rs.Bookmark
End Sub

want to open "frmEnterRecipes" on double click of a selected recipe in datasheet,..........works fine........but

when itjumps to open "frmEnterRecipes" it displays the desired perfectly well, but all of them so that one can cycle around all 1000 of them,

i want to accomplish, when i double click it opens that and only one recipe in edit mode , not all of them. (I am using the same frmEnterRecipes as add and edit mode through switchboard).

i hope i explained well..

thanks in advance

 

[ansentry]

Change this;

strCriteria = "[Name]=" & "'" & Me![Name] & "'"
DoCmd.OpenForm stDocName

To this;

strCriteria = "[Name]=" & "'" & Me![Name] & "'"
DoCmd.OpenForm stDocName[B], , , strCriteria[/B]

You have set the criteria but not used it when the form is opened.

I don't know what this is for;

Set rs = Forms![frmEnterRecipes].Recordset.Clone
rs.FindFirst strCriteria
Forms![frmEnterRecipes].Bookmark = rs.Bookmark

Also I would not use the word Name in a database it is a "reservered word" have a look at the list >> Click Here - Microsoft Site <<

 

[jirrah]

Thanks John, it worked
Actually I am new to this, and vb part of access still I'm struggling (any tutorial suggestion will be great help).
Also can i turn off the navigation buttons by putting some code in same place, so the form opens without the default navigation buttons.
Regarding the field 'Name' working fine so far (you can judge my know how of access!!)
Thanks once again

 

[ansentry]

Glad it worked for you.

Take my advise and change the "Name" to something else if it is say Customer make it CusName EmpName form employee. Do it now before it is too late.

Your can turn off the navigation button by opening the form in design view going to Properties / Format go down until you find Navigation buttons and make it No.

Just another pointer, I notice that you are using name as the criteria for opening the 2nd form, What happens if you have 2 people with the same name "John Smith"

I always use an auto number of a unique number for the "Name". For a Customer you could use CustomerNumber or AutoNumber etc.

Just my 2 cents worth

Good Luck

 

[jirrah]

yes your point taken and am going to change it now,
regarding autonumber i have also some problems (if i can post here), i have many deleted record so the number displayed is not actually the record number, (by the way what is the number shown in between navigation buttons known as), i am searching through this forum regarding the autonumber problem, but cant find the results which suits me so far,,,, any suggestions??
thanks a lot

 

[elbweb]

the auto number is supposed to be used as a specific number for a specific record, the number will never change, and nor will the record that it is representing. that way you use it to relate sub records with a main record, becuase if you have a sub record, you put in the auto number from the main record with that sub record and it will always be related to the main record, cause the number never changes...

if this was a record number, that number would change when a record was deleted before hand and then the reltionship would not be right anymore.

also, the number at the bottom has no real significance other then to show where you are in the great scheme of things. you can change to order that the records are shown, and then these number would be completely different.

now this is just to clarifiy ... im not sure what you were asking so i can't really give you an answer, but feel free to clarify

 

[jirrah]

i have about 600 records in my database, with autonumber field. the records have been written, edited, deleted. the problem is when i display the autonumber it doesn't match the number in the navigation button, aslo it changes when i use filters.
e.g autonumber is showing 569 but the bottom number displays 553,
what i want to specify a number to a particular record, which is unique and identifies that record only, if u delete the record, it also deletes , of course it is also autogenerated.

 

[elbweb]

that should be easily accomlished with relationship, just edit them and choose the 'enforce relational integrity' or something like that, and then the delete option..

 

[jirrah]

thanks, but would appreciate if you can explain in a bit simple way, an example would certainly help

 

[ansentry]

when i display the autonumber it doesn't match the number in the navigation button,

The navigation button displays the count of the records in the table.

The AutoNumber is just a number that is asigned to a record by access. It should not be displayed to the user. When you filter, the navigation buttons show the record count of the filtered records.

Have a look at my sample, it shows navigation button, record count, AutoNumber and CusNumber (generated when you add a new reocrd). Also have a look at the code behind the form.

 

[jirrah]

thanks for help, i managed to get it work,
thanks all