By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

opening specific records in forms ?

P: n/a
Help,

When I am finished entering in data into my form A, I press the save button
that
saves this new data to a new record. However I would like my other form to
open at a specific record, the record that I just finished working on.

Ideas?
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Use the WhereCondition of the OpenForm action.

Assuming a primary key named "ID", that would be:
DoCmd.OpenForm "MyOtherForm", WhereCondition:="ID = " & Me.ID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BLUE WATER" <no**********@nowhere.com> wrote in message
news:NT******************@news04.bloor.is.net.cabl e.rogers.com...

When I am finished entering in data into my form A, I press the save button that
saves this new data to a new record. However I would like my other form to open at a specific record, the record that I just finished working on.

Nov 12 '05 #2

P: n/a
BLUE WATER wrote:
Help,

When I am finished entering in data into my form A, I press the save button
that
saves this new data to a new record. However I would like my other form to
open at a specific record, the record that I just finished working on.

Ideas?

Expanding on Allen Brown's reply...if the form was already open you
could set the filter or find the record.

'setting the filter
Docmd.OpenForm "MyForm"
Forms!MyForm.Form.Filter = "ID = " & Me.ID
Forms!MyForm.Form.FilterOn = True
'finding the record
Dim rst As DAO.Recordset
Docmd.OpenForm "MyForm"
set rst = Forms!MyForm.Form.Recordsetclone
rst.FindFirst "ID = " & Me.ID
Forms!MyForm.Form.BookMark = rst.Bookmark
set rst = Nothing

Allen's method is much simpler but you might find these methods useful
in other situations when working with Access.

Nov 12 '05 #3

P: n/a
Hi Allen,

In my table view, the primary unique identifyer is a variable known as
"Company_ID".

When I replace your "ID" with "Company_ID", the program crashed saying:

Compile error:
Method of data member not found

****
What am I doing wrong here ? I put this code following the
Private Sub commandsaveandentry_click()

Thanks.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...
Use the WhereCondition of the OpenForm action.

Assuming a primary key named "ID", that would be:
DoCmd.OpenForm "MyOtherForm", WhereCondition:="ID = " & Me.ID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BLUE WATER" <no**********@nowhere.com> wrote in message
news:NT******************@news04.bloor.is.net.cabl e.rogers.com...

When I am finished entering in data into my form A, I press the save

button
that
saves this new data to a new record. However I would like my other form

to
open at a specific record, the record that I just finished working on.


Nov 12 '05 #4

P: n/a
Allen, or others,

In my tables there is a unique key that identifies each new company record
with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my
AddNewCompany
form, thus that can be used to open the new form after pressing the button
(known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let
me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany"
form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No
Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub


****
Thanks a million.
Nov 12 '05 #5

P: n/a
BLUE WATER wrote:
Allen, or others,

In my tables there is a unique key that identifies each new company record
with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my
AddNewCompany
form, thus that can be used to open the new form after pressing the button
(known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let
me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany"
form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No
Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub


One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?

There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.

If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName
Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName

THere are multiple ways of doing what you want to do.

Nov 12 '05 #6

P: n/a

"Salad" <oi*@vinegar.com> wrote in message
news:TH*****************@newsread1.news.pas.earthl ink.net...
BLUE WATER wrote:
Allen, or others,

In my tables there is a unique key that identifies each new company record with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my AddNewCompany
form, thus that can be used to open the new form after pressing the button (known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany" form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub
One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?


Yes, it adds a record (new record) to the company files.
There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?
The field is actually Company_ID and not CompanyID. Although the name of
the
feild in the properties is Company ID (notice the space between ID and
Company),
but the variable name is Company_ID.

Yes, I want the InputChemicalsForm to open, to open at the record number
that
corresponds the last used forms Company_ID, so that I don't have to search
every
time I decide to go from this form to the InputChemicalsForm. It should
open at
the record of the company I was using (when I exit the form I just finished
using).
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.
Is this correct ? should it be Company ID or Company_ID ?
If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID
I don't want to add records, since it works already and adds a record by
calling a
function, so i don't think the above is needed is it ?
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName
What do you mean by pass the name ? from the added record ?or ?


Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName

I think if I grab the data from my previously used form it may create a
problem, I want
to keep things very simple for future development.
Let me know the answers to the above questions.

Thanks
Nov 12 '05 #7

P: n/a
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID

I tried to replace the line and my access crashes stating method or member
not found.
It does the same thing if I use Company_ID.

What is the problem here ? Do I have to close the form after this code or
something?

Thanks
"Salad" <oi*@vinegar.com> wrote in message
news:TH*****************@newsread1.news.pas.earthl ink.net...
BLUE WATER wrote:
Allen, or others,

In my tables there is a unique key that identifies each new company record with a number, this
variable is Company_ID as has been typed up in the edit table data view.
Thus I think you are
referring to this unique new identification number that is created from my AddNewCompany
form, thus that can be used to open the new form after pressing the button (known as the
Commandsaveandproductentry button). The new form is called
Input_Chemicals_Form as
viewed from the Access2002 forms list.

Therefore what exactly do I need to fill in the "xxxxxxx" with ? please let me know exactly to the
letter so I don't get bugs again, I must be missing something. I will
remove the ' in the original
VBA code.

Here is my code for the button that is pressed section in my "AddNewCompany" form:

Private Sub Commandsaveandproductentry_Click()
If Len(Me.Company_Legal_Name & vbNullString) > 0 Then
Call AddCompanyInfo
DoCmd.Close acForm, Me.Name

'DoCmd.OpenForm "Input_Chemicals_Form", xxxxxxxx

Else
MsgBox "The Company Name field can not be left blank.", vbCritical, "No Company Name Error"
Me.Company_Legal_Name.SetFocus
End If
End Sub


One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?

There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.

If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName
Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName

THere are multiple ways of doing what you want to do.

Nov 12 '05 #8

P: n/a
On Sat, 10 Apr 2004 22:55:24 +0200, BLUE WATER wrote
(in message <0b*****************@news04.bloor.is.net.cable.rog ers.com>):
DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
First of all: I would use NamedArguments, i.e.:

DOcmd.Openform FormName:="Input_Chemicals_Form", _
WhereCondition:="[CompanyID] = " & Me.companyID
I tried to replace the line and my access crashes stating method or member
not found. It does the same thing if I use Company_ID.

What is the problem here ? Do I have to close the form after this code or
something?


The main error IMHO is that you close your form and then refer
to a form-control or field in the recordsource, but that information
is gone after a close...
First Open the form and then close the calling form.

Anyway a bit more background:

You don't provide enough information to solve your problem.
You really have to your table structure and relation between the tables
and the recordsources you use for your forms (and the function you use
for adding new Companies).

Anyway, I assume you have a one to many relship between your companies
and products.

For your intention the product form should have the company PK (and other
fields as desired) in the recordsource and the product fields should be
in the recordsource of a subform on that same form.
Link the subform to the main form on the FK<->PK.

Now: What does your Company_save function do? Insert via SQL/Recordset or
just save the Form data (with me.Dirty = False)?
If your inserting a new company and then want to open the product form
you have to be sure to provide the correct PK (CompanyID, FK in the Product-
table) to the Product form.

In the simplest form do a:
If me.dirty then
me.dirty = false
End If

before you open the product form.
Then in the Product-Form-opening code provide the PK of the company in the
WhereCondition-Argument.

OK, pseudo code:

Private Sub YourProductOpenButton_Click()
If Me.Dirty Then
Me.dirty = false
End If
DoCmd.OpenForm FormName:="ProductFormName", _
WhereCondition:= "CompanyPKfield = " & Me.CompanyPKField
(or: WhereCondition:="CompanyPKfield = " & Me.FormControlThatHoldsCompanyPK
'And now you can close the Company Form...
End Sub

CompanyPKfield is the field-name of the CompanyPK-Field in the Recordsource
of the Products-Form Recordsource (main form not subform).
This way access will automatically insert the right FK value into the
products table when entering new Products for a company.

There are a zillion other ways to realize such a setup with a main
and a detail table.

Michael

Nov 12 '05 #9

P: n/a
BLUE WATER wrote:
One thing you do not show is AddCompanyInfo code. Does it add a record
to the Company file?

Yes, it adds a record (new record) to the company files.
There is the field CompanyID. Do you want to open the form
InputChemicalsForm to that company ID?

The field is actually Company_ID and not CompanyID. Although the name of
the
feild in the properties is Company ID (notice the space between ID and
Company),
but the variable name is Company_ID.


In a table, you have a field.
In a form (if it is bound to a table) you have
ControlSource (table field). See data tab
and
Name (how you reference it in code). See other tab

I would like you do do something. Click on queries tab, click
New/Design View. Select any table, click Add, then close. Drag the
field down. Let's pretend it is called Date_Field. I could enter
Happy Days Are Here Again : Date_Field
Substitute Date_field with the field name. Now press View/SQL. If you
look at the SQL code it will be something like
Select Date_Field As [Happy Days Are Here Again]....

You will notice that Happy Days Are Here Again are surrounded in
brackets. If you create table fields with spaces, and you reference the
field via a recordset, you need to surround the field name in brackets.
If you have a space in a control's name on a form, you reference it
the same. Ex: Me.[Happy Days Are Here Again]

So maybe your where filter when opening should be
[Company Name] = '" & Me.Company_Name & "'"
or
[Company ID] = " & Me.Company_ID

We that are trying to help you may use CompanyID or Company_ID or
Company ID...it is up to you to experiment a bit and modify our code to
meet your needs.

Yes, I want the InputChemicalsForm to open, to open at the record number
that
corresponds the last used forms Company_ID, so that I don't have to search
every
time I decide to go from this form to the InputChemicalsForm. It should
open at
the record of the company I was using (when I exit the form I just finished
using).

DOcmd.Openform "Input_Chemicals_Form",,,"[CompanyID] = " & Me.companyID
will open the form to the chemicals of that company id.

Is this correct ? should it be Company ID or Company_ID ?


In the form, check the NAME field in the OTHER tab of the property sheet.
If you want to add a record, you could enter
Docmd.Openform "Input_Chemicals_Form",,,,acFormAdd
Forms!Input_Chemicals_Form!CompanyID = Me.CompanyID

I don't want to add records, since it works already and adds a record by
calling a
function, so i don't think the above is needed is it ?


No. But someday you might want to re-read this. You have been given a
good lesson. Lot's of good stuff has been given to you.
You could even pass the name with
Forms!Input_Chemicals_Form!CompanyName = Me.CompanyName

What do you mean by pass the name ? from the added record ?or ?


Sure. I am in a form. I call a new form. Before I close the form I am
in I can pass the newly opened form information from the current form.

It would be worthwhile to learn about the Form object module if you want
to program in Access and manipulate forms.
Or when you open Input_Chemicals_Form you can gram data from the current
form. In your OnOpen event for Input_Chemicals_Form you could enter
Me.CompanyName = Forms!YourCurrentForm!CompanyName


I think if I grab the data from my previously used form it may create a
problem, I want
to keep things very simple for future development.


You should do what makes you comfortable.

Let me know the answers to the above questions.


I'm hoping I did.
Thanks


Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.