Jump to record that satisfy some criteria

[Guest]

Hi

How do I query data in a table from within a form using a VB command? I want
to search for a record that has a specific date and name in a table and
then moves to this reocrd in the form so that I can basically edit the
information for that specific record.

Someone (Dennis) already help me, but for some unexplained reason his
techinique doesn't work 100% (Even though it should).

He told me to do the following:

TempID = DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "' And
[Date] = #" & mDate & "#")
DoCmd.GoToControl Forms!Timesheet!Field.ID
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acCurrent, True

My table and form has the name of "Timesheet". It looks up a unique ID for a
record that has the "CurrentUser" in the Names field and "mdate" (Which I
enter from the form) in the Date field and assigns this ID to TempID. It then
goes to ID control to ensure that the "FindRecord" command searches in that
field and then it should go to the record that has the value "TempID" in the
ID field.

Is their an easier or other way to do this?

Thanks!

Thanks!
R

 

[John Vinson]

Hi

How do I query data in a table from within a form using a VB command? I want
to search for a record that has a specific date and name in a table and
then moves to this reocrd in the form so that I can basically edit the
information for that specific record.

Someone (Dennis) already help me, but for some unexplained reason his
techinique doesn't work 100% (Even though it should).

He told me to do the following:

TempID = DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "' And
[Date] = #" & mDate & "#")
DoCmd.GoToControl Forms!Timesheet!Field.ID
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acCurrent, True

My table and form has the name of "Timesheet". It looks up a unique ID for a
record that has the "CurrentUser" in the Names field and "mdate" (Which I
enter from the form) in the Date field and assigns this ID to TempID. It then
goes to ID control to ensure that the "FindRecord" command searches in that
field and then it should go to the record that has the value "TempID" in the
ID field.

The code looks correct. What is going wrong? Under what circumstances
does it fail to find the right record?

There is another way to synch to a record (using the Form's
RecordsourceClone) but it's no simpler than this method, and if this
fails, so will the other.

I could make three suggestions which might or might not make a
difference:

1. Rename the field Date. It's a reserved word, for the Date()
function; Access can and will get confused.
2. Rather than putting a value from a form control into mdate, and
then using mdate directly, you can use a reference to the form control
directly; I'll assume it's named txtFindDate.
3. Explicitly format the search date in Access' preferred mm/dd/yyyy
format:

TempID = DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "'
And [NewDateField] = #" & Format(Me!txtFindDate, "mm\/dd\/yyyy") &
"#")


John W. Vinson[MVP]

 

[Guest]

John

I don't want to change the part for "TempID = ....", because that part is
working fine. It gives the correct unique ID for the correct record. Where
this technique fails, is to move to this record:

DoCmd.GoToControl Forms!Timesheet!Field.ID
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acCurrent, True

I am using a form within a form, I don't know if this might make a
difference. The Main form is linked to the table where ID is located. This
command is situated in the subform though.

Any other suggestions...?

Thanks!

Hi

How do I query data in a table from within a form using a VB command? I want
to search for a record that has a specific date and name in a table and
then moves to this reocrd in the form so that I can basically edit the
information for that specific record.

Someone (Dennis) already help me, but for some unexplained reason his
techinique doesn't work 100% (Even though it should).

He told me to do the following:

TempID = DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "' And
[Date] = #" & mDate & "#")
DoCmd.GoToControl Forms!Timesheet!Field.ID
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acCurrent, True

My table and form has the name of "Timesheet". It looks up a unique ID for a
record that has the "CurrentUser" in the Names field and "mdate" (Which I
enter from the form) in the Date field and assigns this ID to TempID. It then
goes to ID control to ensure that the "FindRecord" command searches in that
field and then it should go to the record that has the value "TempID" in the
ID field.

The code looks correct. What is going wrong? Under what circumstances
does it fail to find the right record?

There is another way to synch to a record (using the Form's
RecordsourceClone) but it's no simpler than this method, and if this
fails, so will the other.

I could make three suggestions which might or might not make a
difference:

1. Rename the field Date. It's a reserved word, for the Date()
function; Access can and will get confused.
2. Rather than putting a value from a form control into mdate, and
then using mdate directly, you can use a reference to the form control
directly; I'll assume it's named txtFindDate.
3. Explicitly format the search date in Access' preferred mm/dd/yyyy
format:

TempID = DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "'
And [NewDateField] = #" & Format(Me!txtFindDate, "mm\/dd\/yyyy") &
"#")


John W. Vinson[MVP]

 

[John Vinson]

John

I don't want to change the part for "TempID = ....", because that part is
working fine. It gives the correct unique ID for the correct record. Where
this technique fails, is to move to this record:

DoCmd.GoToControl Forms!Timesheet!Field.ID
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acCurrent, True

I am using a form within a form, I don't know if this might make a
difference. The Main form is linked to the table where ID is located. This
command is situated in the subform though.

Any other suggestions...?

Well, that's the problem then!

The command being in the subform is tricky though. If you navigate to
a new mainform record, it will requery the Subform and change all the
records that are currently there. Is that acceptable?

I'd suggest using recordset manipulation on the mainform's
Recordsource:

Dim rs As DAO.Recordset
Set rs = Parent.RecordsetClone ' get the Parent form's data
rs.FindFirst "[ID] = " & TempID
If rs.NoMatch Then
MsgBox "This ID was not found", vbOKOnly
Else
Parent.SetFocus ' set focus to the parent form
Parent!SomeControlName.SetFocus ' and to a control there
Parent.Bookmark = rs.Bookmark ' synchronize the form
End If
Set rs = Nothing ' clean up after yourself

John W. Vinson[MVP]

 

[Guest]

I tried your code, but I get an error message on the following line:

rs As DAO.Recordset

I get the following error:

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

User-defined type not defined
---------------------------
OK Help
---------------------------

Is this possibly that I must use the ADo format instead of the DAO format?

Thanks!

John

I don't want to change the part for "TempID = ....", because that part is
working fine. It gives the correct unique ID for the correct record. Where
this technique fails, is to move to this record:

DoCmd.GoToControl Forms!Timesheet!Field.ID
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acCurrent, True

I am using a form within a form, I don't know if this might make a
difference. The Main form is linked to the table where ID is located. This
command is situated in the subform though.

Any other suggestions...?

Well, that's the problem then!

The command being in the subform is tricky though. If you navigate to
a new mainform record, it will requery the Subform and change all the
records that are currently there. Is that acceptable?

I'd suggest using recordset manipulation on the mainform's
Recordsource:

Dim rs As DAO.Recordset
Set rs = Parent.RecordsetClone ' get the Parent form's data
rs.FindFirst "[ID] = " & TempID
If rs.NoMatch Then
MsgBox "This ID was not found", vbOKOnly
Else
Parent.SetFocus ' set focus to the parent form
Parent!SomeControlName.SetFocus ' and to a control there
Parent.Bookmark = rs.Bookmark ' synchronize the form
End If
Set rs = Nothing ' clean up after yourself

John W. Vinson[MVP]

 

[John Vinson]

Is this possibly that I must use the ADo format instead of the DAO format?

The reverse: you need to use the DAO library. Open the VBA editor,
select Tools... References, and scroll down to

Microsoft DAO x.xx Object Library

and check the largest-numbered version.

John W. Vinson[MVP]

 

[Guest]

John,

Thanks...your code doesn't give an error anymore. It tell me that there was
no ID found, but on the records that has one of these ID's, it simply does
nothing. I entered your code exactly the same as you gave it to me below. I
don't know these commands well...Am I suppose to replace something like
"SomeControlName" with the name of some control...? This code is placed in
the click event of a calender control, which is part of the subform.

Dim rs As DAO.Recordset
Set rs = Parent.RecordsetClone ' get the Parent form's data
rs.FindFirst "[ID] = " & TempID
If rs.NoMatch Then
MsgBox "This ID was not found", vbOKOnly
Else
Parent.SetFocus ' set focus to the parent form
Parent!SomeControlName.SetFocus ' and to a control there
Parent.Bookmark = rs.Bookmark ' synchronize the form
End If
Set rs = Nothing ' clean up after yourself

 

[Guest]

Don't worry, I got it right!

Thanks!

The reverse: you need to use the DAO library. Open the VBA editor,
select Tools... References, and scroll down to

Microsoft DAO x.xx Object Library

and check the largest-numbered version.

John W. Vinson[MVP]