Create Table Record on Current of Form

[magmike]

I have a table called LastVisitedRecord. There is but one field in
that table called "lvCompanyID". I'd like to use the OnCurrent event
of my record form to insert the record id (CompanyID) into a new
record in the LastVisitedRecord table.

After that, I'd like to use a query I've created qLastVisitedRecord
which only shows the top 1 record of the LastVisitedRecord table to
query my contact form by default, showing all records, but skipping to
the record whose CompanyID field matches the sole record in the query
qLastVisitedRecord.

Anybody got an idea on how to do this? Thanks!

magmike

 

[Jeanette Cunningham]

Hi,
not sure I am fully understanding what you are doing.
Your form shows values for Company, when your form opens or goes to a new
record you want to add the CompanyID to the table LastVisitedRecord?

You do this with an append query.
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( " & me.txtCompanyID & " ) "

if Company ID is a text field, use
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( """ & me.txtCompanyID & """ ) "

below shows how to put the code into the form's current event:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database

Set db = DBEngine(0)(0)
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( " & me.txtCompanyID & " ) "
db.Execute strSQL
Set db = Nothing
End Sub

Jeanette Cunningham

 

[magmike]

Hi,
not sure I am fully understanding what you are doing.
Your form shows values for Company, when your form opens or goes to a new
record you want to add the CompanyID to the table LastVisitedRecord?

You do this with an append query.
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
                & " VALUES ( " & me.txtCompanyID & " ) "

if Company ID is a text field, use
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
                & " VALUES ( """ & me.txtCompanyID & """ )"

below shows how to put the code into the form's current event:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database

    Set db = DBEngine(0)(0)
    strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
                & " VALUES ( " & me.txtCompanyID & " ) "
    db.Execute strSQL
    Set db = Nothing
End Sub

Jeanette Cunningham









- Show quoted text -

That's perfect, thanks!

Now, how could I set the form to still show all the records as it does
now (only one at a time, of course) but to skip to the last visited
record? I have created a query that only shows the top 1 record in
that table, which would be the last visited record. I'd like to open
the form and it go to the last record the form showed.

 

[Jeanette Cunningham]

Hi,
not sure how your form is setup.
You are asking the form to only show the record where the companyID is the
same as the companyID you just appended to LastVisitedRecord?
but then be able to choose any record?
One way to do this is to use a filter for the form.
When the form opens/loads, you set the form's filter to the CompanyID you
want it to move to.
You will need a button to remove the filter when the user wants to see other
records ( I think - I don't usually use this strategy myself)

To get the CompanyID for the filter you can use DLookup to lookup the
lvCompanyID from qLastVisitedRecord
If filters are new to you, there are some pointers and examples on
www.allenbrowne.com


Jeanette Cunningham

Hi,
not sure I am fully understanding what you are doing.
Your form shows values for Company, when your form opens or goes to a new
record you want to add the CompanyID to the table LastVisitedRecord?

You do this with an append query.
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( " & me.txtCompanyID & " ) "

if Company ID is a text field, use
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( """ & me.txtCompanyID & """ ) "

below shows how to put the code into the form's current event:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database

Set db = DBEngine(0)(0)
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( " & me.txtCompanyID & " ) "
db.Execute strSQL
Set db = Nothing
End Sub

Jeanette Cunningham









- Show quoted text -

That's perfect, thanks!

Now, how could I set the form to still show all the records as it does
now (only one at a time, of course) but to skip to the last visited
record? I have created a query that only shows the top 1 record in
that table, which would be the last visited record. I'd like to open
the form and it go to the last record the form showed.

 

[magmike]

Hi,
not sure how your form is setup.
You are asking the form to only show the record where the companyID is the
same as the companyID you just appended to LastVisitedRecord?
but then be able to choose any record?
One way to do this is to use a filter for the form.
When the form opens/loads, you set the form's filter to the CompanyID you
want it to move to.
You will need a button to remove the filter when the user wants to see other
records ( I think - I don't usually use this strategy myself)

To get the CompanyID for the filter you can use DLookup to lookup the
lvCompanyID from qLastVisitedRecord
If filters are new to you, there are some pointers and examples onwww.allenbrowne.com

Jeanette Cunningham












That's perfect, thanks!

Now, how could I set the form to still show all the records as it does
now (only one at a time, of course) but to skip to the last visited
record? I have created a query that only shows the top 1 record in
that table, which would be the last visited record. I'd like to open
the form and it go to the last record the form showed.- Hide quoted text -

- Show quoted text -

My form currently shows every record available, except only one at a
time of course since it is a single record form (not continuous).
Currently, record #1 (the first record created) always shows first.
I'd like it to use qLastRecordVisited to move to the matching record
in the recordset, rather than filter to just one record, and then need
to "un" filter the form. This is what I am not sure how to do.

 

[Jeanette Cunningham]

Hi,
here is a way for a user to choose a Company from a combo and then the form
will go to that record.
I am guessing this is what you are after.
This way you don't need the table LastVisitedRecord

--put an unbound combo in the header section of your form
--make its row source a query with 2 fields, CompanyID and CompanyName
--hide the 1st column of the combo
--user selects a company in the combo and the form shows the information for
that company'
--when user selects a different company in the combo, the form shows the
info for it and so on

If this sounds like what you want, you can use the toolbox wizard to put the
combo on the header of your form.
In the wizard, choose the 3rd option, Find a record on my form based on the
value I selected in my combo.

Jeanette Cunningham

Hi,
not sure how your form is setup.
You are asking the form to only show the record where the companyID is the
same as the companyID you just appended to LastVisitedRecord?
but then be able to choose any record?
One way to do this is to use a filter for the form.
When the form opens/loads, you set the form's filter to the CompanyID you
want it to move to.
You will need a button to remove the filter when the user wants to see
other
records ( I think - I don't usually use this strategy myself)

To get the CompanyID for the filter you can use DLookup to lookup the
lvCompanyID from qLastVisitedRecord
If filters are new to you, there are some pointers and examples
onwww.allenbrowne.com

Jeanette Cunningham












That's perfect, thanks!

Now, how could I set the form to still show all the records as it does
now (only one at a time, of course) but to skip to the last visited
record? I have created a query that only shows the top 1 record in
that table, which would be the last visited record. I'd like to open
the form and it go to the last record the form showed.- Hide quoted text -

- Show quoted text -

My form currently shows every record available, except only one at a
time of course since it is a single record form (not continuous).
Currently, record #1 (the first record created) always shows first.
I'd like it to use qLastRecordVisited to move to the matching record
in the recordset, rather than filter to just one record, and then need
to "un" filter the form. This is what I am not sure how to do.