Query records from VB

[Rudi]

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
returns the recordnumber so that I can jump to this record...how do I do this?

Thanks!
R

 

[Dennis]

If each record in the table has a unique ID then you can use the DLookUp
function.
SomeName and SomeDate are the names of the name & date field in your table
and SpecificName and SpecificDate are the fields on your form that you are
checking against.

dim TempID as Long

TempID = DLookUp("UniqueID","YourTable","[SomeName] = '" & Me.SpecificName &
"' And [SomeDate] = #" & Me.SpecificDate & "#")

If TempID is returned then you can open the form and go to the TempID record.

 

[Rudi]

Hi

Thanks for the help so far...

I can now get the TempID for a specific record, but how do I determine the
record number and/or go to the record?

Here is what I would use to go to the record if I can determine the record
number from the TempID:

DoCmd.GoToRecord acDataForm, Me.Parent.Name, acGoTo, RecordNumber

If each record in the table has a unique ID then you can use the DLookUp
function.
SomeName and SomeDate are the names of the name & date field in your table
and SpecificName and SpecificDate are the fields on your form that you are
checking against.

dim TempID as Long

TempID = DLookUp("UniqueID","YourTable","[SomeName] = '" & Me.SpecificName &
"' And [SomeDate] = #" & Me.SpecificDate & "#")

If TempID is returned then you can open the form and go to the TempID record.

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
returns the recordnumber so that I can jump to this record...how do I do this?

Thanks!
R

 

[Dennis]

Use the find instead of the goto
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acAll, True

Hi

Thanks for the help so far...

I can now get the TempID for a specific record, but how do I determine the
record number and/or go to the record?

Here is what I would use to go to the record if I can determine the record
number from the TempID:

DoCmd.GoToRecord acDataForm, Me.Parent.Name, acGoTo, RecordNumber

If each record in the table has a unique ID then you can use the DLookUp
function.
SomeName and SomeDate are the names of the name & date field in your table
and SpecificName and SpecificDate are the fields on your form that you are
checking against.

dim TempID as Long

TempID = DLookUp("UniqueID","YourTable","[SomeName] = '" & Me.SpecificName &
"' And [SomeDate] = #" & Me.SpecificDate & "#")

If TempID is returned then you can open the form and go to the TempID record.

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
returns the recordnumber so that I can jump to this record...how do I do this?

Thanks!
R

 

[Rudi]

Is there no other way that I could just retrieve the record number and use
the GoToRecord command?

The first problem I have encountered with the statement you gave below, is
that it does exactly nothing. Should it find the record and automatically
move to that record? Secondly, the ID that I have used is just a number, so
the TempID will also just be a number. I have other fields that contains only
numbers as well. From what I understand from the FindRecord command, it
doesn't search within a certain field in your record that you specify like ID
or name, it just looks for the first occurence of the parameter it searches
for (TempID in this case). This means that if my TempID has the value of 32
and I use the FindRecord command it might find the value 32 in my "Hours"
field in the wrong record.

Any ideas?

Use the find instead of the goto
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acAll, True

Hi

Thanks for the help so far...

I can now get the TempID for a specific record, but how do I determine the
record number and/or go to the record?

Here is what I would use to go to the record if I can determine the record
number from the TempID:

DoCmd.GoToRecord acDataForm, Me.Parent.Name, acGoTo, RecordNumber

If each record in the table has a unique ID then you can use the DLookUp
function.
SomeName and SomeDate are the names of the name & date field in your table
and SpecificName and SpecificDate are the fields on your form that you are
checking against.

dim TempID as Long

TempID = DLookUp("UniqueID","YourTable","[SomeName] = '" & Me.SpecificName &
"' And [SomeDate] = #" & Me.SpecificDate & "#")

If TempID is returned then you can open the form and go to the TempID record.

:

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
returns the recordnumber so that I can jump to this record...how do I do this?

Thanks!
R

 

[Dennis]

Not sure if you can get the record number from knowing the TempID. You could
put this in before your find so that the TempID is matched against the
correct field.
DoCmd.GoToControl Forms!FormName!Field.Name
Modify your find then to be
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acCurrent, True

This should find the record and automatically move to it

Is there no other way that I could just retrieve the record number and use
the GoToRecord command?

The first problem I have encountered with the statement you gave below, is
that it does exactly nothing. Should it find the record and automatically
move to that record? Secondly, the ID that I have used is just a number, so
the TempID will also just be a number. I have other fields that contains only
numbers as well. From what I understand from the FindRecord command, it
doesn't search within a certain field in your record that you specify like ID
or name, it just looks for the first occurence of the parameter it searches
for (TempID in this case). This means that if my TempID has the value of 32
and I use the FindRecord command it might find the value 32 in my "Hours"
field in the wrong record.

Any ideas?

Use the find instead of the goto
DoCmd.FindRecord TempID, acEntire, , acSearchAll, , acAll, True

Hi

Thanks for the help so far...

I can now get the TempID for a specific record, but how do I determine the
record number and/or go to the record?

Here is what I would use to go to the record if I can determine the record
number from the TempID:

DoCmd.GoToRecord acDataForm, Me.Parent.Name, acGoTo, RecordNumber




:

If each record in the table has a unique ID then you can use the DLookUp
function.
SomeName and SomeDate are the names of the name & date field in your table
and SpecificName and SpecificDate are the fields on your form that you are
checking against.

dim TempID as Long

TempID = DLookUp("UniqueID","YourTable","[SomeName] = '" & Me.SpecificName &
"' And [SomeDate] = #" & Me.SpecificDate & "#")

If TempID is returned then you can open the form and go to the TempID record.

:

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
returns the recordnumber so that I can jump to this record...how do I do this?

Thanks!
R