Open form based on the selection from a text box

[Skot]

Okay, i've spent half a day going through all the different question here in
the newsgroups to try and find a solution but have had no luck.

I need to open a form, but on a specific record. I have a control button
that has the follow commond onClick;

DoCmd.OpenForm "Job1", , , "[Job No]=" & Me.[Job No]

No... the curious problem is this... The first form is a continious, showing
all records based on a filter. I think have this "Open Form" button on each
form.

When i click it on the first two lines, they seem to open fine, but from
there on... it seems to open a new record.

I used to have a "NewRecord" set at the onOpen, but got rid of that because
it obvoiusly caused me grief when trying to open a specific record... could
there be somewhere that is still floating around?

Is there a better OpenForm command to use? I've tried the one through the
Controls Wizard and that does much the same thing.

 

[Evi]

I've read this a couple of times and it still isn't making any sense. See if
this is what is happening.
You have a continuous form - Form 1.
Each record has a button. If you click a button you want to open another
form, Form 2, filtered so that you only show the details for the record
whose button you have clicked.
OK?
Your code is very straight forward and that ought to work *unless* Job No is
a text field.

You seem to be saying that it works for the first 2 records in Form 1, ie
Form 2 opens correctly filtered, so I must assume that Job No is a number
field or it wouldn't work at all.
Then you say that Form 2, opens and shows no records.

Obviously, if you had New Record set on Form 2 then that would stop your
filter from working so you correctly deleted that.
What code do you have in Form 2? Open its code module and (if necessary)
paste any code here which you do not entirely understand.
Is there any data in Form 2 for the other records which open as blanks when
you use Open Form

Check Form2's Properties and see if there is still some Filter or Order By
line that refers to something that no longer exists - this is unlikely
because the correct filtering of the first 2 records should have sorted
that.

Evi

 

[Skot]

Evi,

Thanks a heap for your response... while i was replying to you i found out
what was happening. My filter was showing me all my records (Form1), but
Form2 (which is the form i'm trying to open) has some form of filter on it
that prevents it from opening particular records. Ie. records 3-7 don't open
but 8 does etc. etc.

Now i believe the reason for particular records not being opened is with my
code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub
---------------------------

Now this code is to allow for two a sycronised combo boxes (ie. limit what
can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i basically
copied it out of one of these newsgroup so i don't understand it fully.

Thanks a heap again.

I've read this a couple of times and it still isn't making any sense. See if
this is what is happening.
You have a continuous form - Form 1.
Each record has a button. If you click a button you want to open another
form, Form 2, filtered so that you only show the details for the record
whose button you have clicked.
OK?
Your code is very straight forward and that ought to work *unless* Job No is
a text field.

You seem to be saying that it works for the first 2 records in Form 1, ie
Form 2 opens correctly filtered, so I must assume that Job No is a number
field or it wouldn't work at all.
Then you say that Form 2, opens and shows no records.

Obviously, if you had New Record set on Form 2 then that would stop your
filter from working so you correctly deleted that.
What code do you have in Form 2? Open its code module and (if necessary)
paste any code here which you do not entirely understand.
Is there any data in Form 2 for the other records which open as blanks when
you use Open Form

Check Form2's Properties and see if there is still some Filter or Order By
line that refers to something that no longer exists - this is unlikely
because the correct filtering of the first 2 records should have sorted
that.

Evi

Okay, i've spent half a day going through all the different question here in
the newsgroups to try and find a solution but have had no luck.

I need to open a form, but on a specific record. I have a control button
that has the follow commond onClick;

DoCmd.OpenForm "Job1", , , "[Job No]=" & Me.[Job No]

No... the curious problem is this... The first form is a continious, showing
all records based on a filter. I think have this "Open Form" button on each
form.

When i click it on the first two lines, they seem to open fine, but from
there on... it seems to open a new record.

I used to have a "NewRecord" set at the onOpen, but got rid of that because
it obvoiusly caused me grief when trying to open a specific record... could
there be somewhere that is still floating around?

Is there a better OpenForm command to use? I've tried the one through the
Controls Wizard and that does much the same thing.

 

[Evi]

You may have got your code here:
http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html
Is Supervisor both the name of an actual field in your form's Record Source
and the name of an unbound combo box? (It's usually best to avoid calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular item in
an unbound list or combo box using the order that the item appears in - the
first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound one. Is it?

I suspect strongly that there are things that you haven't told us. For
instance, are you trying to filter an unbound form? What is the purpose of
the Unbound combo? Are you actually trying to filter a form at all or are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo then the
method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it is based on
the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and having
BuildID BuilderName, SupervisorID from the Builders table.
Under SupervisorID, in the Criteria Row, you would type
Forms![TheNameOfYourForm].[cboBuilder]

The Row Source of cboBuilder would be this query

cboSupervisor's AfterUpdate Event will have
Me.cboBuilder.Requery.
Me.cboBuilder.Value = Null
'sets what you see in cboBuilder to Null so that you don't have the value
'from the last filter showing

Evi




you would then open the Properties box of cboBuilder and next to Row Source
you would type

Evi,

Thanks a heap for your response... while i was replying to you i found out
what was happening. My filter was showing me all my records (Form1), but
Form2 (which is the form i'm trying to open) has some form of filter on it
that prevents it from opening particular records. Ie. records 3-7 don't open
but 8 does etc. etc.

Now i believe the reason for particular records not being opened is with my
code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub
---------------------------

Now this code is to allow for two a sycronised combo boxes (ie. limit what
can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i basically
copied it out of one of these newsgroup so i don't understand it fully.

Thanks a heap again.

I've read this a couple of times and it still isn't making any sense. See if
this is what is happening.
You have a continuous form - Form 1.
Each record has a button. If you click a button you want to open another
form, Form 2, filtered so that you only show the details for the record
whose button you have clicked.
OK?
Your code is very straight forward and that ought to work *unless* Job No is
a text field.

You seem to be saying that it works for the first 2 records in Form 1, ie
Form 2 opens correctly filtered, so I must assume that Job No is a number
field or it wouldn't work at all.
Then you say that Form 2, opens and shows no records.

Obviously, if you had New Record set on Form 2 then that would stop your
filter from working so you correctly deleted that.
What code do you have in Form 2? Open its code module and (if necessary)
paste any code here which you do not entirely understand.
Is there any data in Form 2 for the other records which open as blanks when
you use Open Form

Check Form2's Properties and see if there is still some Filter or Order By
line that refers to something that no longer exists - this is unlikely
because the correct filtering of the first 2 records should have sorted
that.

Evi

Okay, i've spent half a day going through all the different question

here
in

the newsgroups to try and find a solution but have had no luck.

I need to open a form, but on a specific record. I have a control button
that has the follow commond onClick;

DoCmd.OpenForm "Job1", , , "[Job No]=" & Me.[Job No]

No... the curious problem is this... The first form is a continious, showing
all records based on a filter. I think have this "Open Form" button on each
form.

When i click it on the first two lines, they seem to open fine, but from
there on... it seems to open a new record.

I used to have a "NewRecord" set at the onOpen, but got rid of that because
it obvoiusly caused me grief when trying to open a specific record... could
there be somewhere that is still floating around?

Is there a better OpenForm command to use? I've tried the one through the
Controls Wizard and that does much the same thing.

 

[Skot]

Evi,

Thanks again for ur response... I really appreciate it.

My combo box is actually bound (well, i think it is)..

Anyway, I've had a bit of a look at the code again and tried a few of your
suggestions. When I change the line to; "Me.cboBuilder.Value = Null" i get an
error message; "You tried to assign the Null value to a variable that is not
a Variant data type". I've got rid of the onLoad procedure and checked to
make sure my query matches what you wrote but it still doesn't work. I'm not
overly concerned because I realised that the only reason why I have blank
fields in the Supervisor section is just for debugging... and i can probably
just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for you...
I have a TAB control on a page. I want to add a control Box on say "Page1"
that will create a new record on "Page2" (subform called "Form2"), fill in
the fields and save it... without leaving "Page1".

Do I make sense and is it possible? I had a control box on Page2 that said;
DoCmd.GoToRecord, , acNewRec
Me.Subject = "Job confirmed"
Me.Notes = "These are notes"

.... that worked, but I don't know how to make it work if the control box is
on a different Page... and different form.

Thanks again
Scott.

You may have got your code here:
http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html
Is Supervisor both the name of an actual field in your form's Record Source
and the name of an unbound combo box? (It's usually best to avoid calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular item in
an unbound list or combo box using the order that the item appears in - the
first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound one. Is it?

I suspect strongly that there are things that you haven't told us. For
instance, are you trying to filter an unbound form? What is the purpose of
the Unbound combo? Are you actually trying to filter a form at all or are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo then the
method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it is based on
the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and having
BuildID BuilderName, SupervisorID from the Builders table.
Under SupervisorID, in the Criteria Row, you would type
Forms![TheNameOfYourForm].[cboBuilder]

The Row Source of cboBuilder would be this query

cboSupervisor's AfterUpdate Event will have
Me.cboBuilder.Requery.
Me.cboBuilder.Value = Null
'sets what you see in cboBuilder to Null so that you don't have the value
'from the last filter showing

Evi




you would then open the Properties box of cboBuilder and next to Row Source
you would type

Evi,

Thanks a heap for your response... while i was replying to you i found out
what was happening. My filter was showing me all my records (Form1), but
Form2 (which is the form i'm trying to open) has some form of filter on it
that prevents it from opening particular records. Ie. records 3-7 don't open
but 8 does etc. etc.

Now i believe the reason for particular records not being opened is with my
code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub
---------------------------

Now this code is to allow for two a sycronised combo boxes (ie. limit what
can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i basically
copied it out of one of these newsgroup so i don't understand it fully.

Thanks a heap again.

I've read this a couple of times and it still isn't making any sense. See if
this is what is happening.
You have a continuous form - Form 1.
Each record has a button. If you click a button you want to open another
form, Form 2, filtered so that you only show the details for the record
whose button you have clicked.
OK?
Your code is very straight forward and that ought to work *unless* Job No is
a text field.

You seem to be saying that it works for the first 2 records in Form 1, ie
Form 2 opens correctly filtered, so I must assume that Job No is a number
field or it wouldn't work at all.
Then you say that Form 2, opens and shows no records.

Obviously, if you had New Record set on Form 2 then that would stop your
filter from working so you correctly deleted that.
What code do you have in Form 2? Open its code module and (if necessary)
paste any code here which you do not entirely understand.
Is there any data in Form 2 for the other records which open as blanks when
you use Open Form

Check Form2's Properties and see if there is still some Filter or Order By
line that refers to something that no longer exists - this is unlikely
because the correct filtering of the first 2 records should have sorted
that.

Evi





Okay, i've spent half a day going through all the different question here
in
the newsgroups to try and find a solution but have had no luck.

I need to open a form, but on a specific record. I have a control button
that has the follow commond onClick;

DoCmd.OpenForm "Job1", , , "[Job No]=" & Me.[Job No]

No... the curious problem is this... The first form is a continious,
showing
all records based on a filter. I think have this "Open Form" button on
each
form.

When i click it on the first two lines, they seem to open fine, but from
there on... it seems to open a new record.

I used to have a "NewRecord" set at the onOpen, but got rid of that
because
it obvoiusly caused me grief when trying to open a specific record...
could
there be somewhere that is still floating around?

Is there a better OpenForm command to use? I've tried the one through the
Controls Wizard and that does much the same thing.

 

[Evi]

If your combo box is bound then you can't set its value to null (bound means
that if you changed the BuilderID field in your table, your combo box's
display would automatically change from bill smith to george brown and if
you changed the combo so that a different name from Bill Smith appeared then
the ID field in your table would change) That code you have used is for an
Unbound combo. You can put an unbound combo into your form. Base it on your
builders table but when it comes to the bit in the Wizard where is says

Your tab question: It's not the controls on page 2 that you will need to
fill in, but the table on which page 2 is based.
Is Page 2 based on a different table from Page 1?

If yes, then the basic idea is that when you have finished adding a new
record to Page 1 (After Insert Event), an Append Query runs which adds
something
to the table on which Page 2 is built.
Presumably Page 2 is a linked table which contains a foreign key field which
is the Primary field of Page 1's table.

The code in the After Insert Event would look something like this (depending
on your field and table names.

Dim MyField1 As Long
Dim MyField1Name As String
Dim MySql As String
Dim MyOtherTable As String

MyOtherTable = "The Name of your table"
MyField1Name = "TheNameOfYourNumberField"
'edit as suits

If Me.Dirty = True Then
Me.Dirty = False
'save the current record.

MyField1 = Me.TheNameOfTheLinkingField

MySql = "INSERT INTO " & MyOtherTable & " ( " & MyFieldName & " )" & "
VALUES ( " & MyField1 & ");"
DoCmd.RunSql MySql

If the values don't show up when you tab to page 2 then use
Me.Form.Requery in the On Click event of the page.



Evi

Evi,

Thanks again for ur response... I really appreciate it.

My combo box is actually bound (well, i think it is)..

Anyway, I've had a bit of a look at the code again and tried a few of your
suggestions. When I change the line to; "Me.cboBuilder.Value = Null" i get an
error message; "You tried to assign the Null value to a variable that is not
a Variant data type". I've got rid of the onLoad procedure and checked to
make sure my query matches what you wrote but it still doesn't work. I'm not
overly concerned because I realised that the only reason why I have blank
fields in the Supervisor section is just for debugging... and i can probably
just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for you...
I have a TAB control on a page. I want to add a control Box on say "Page1"
that will create a new record on "Page2" (subform called "Form2"), fill in
the fields and save it... without leaving "Page1".

Do I make sense and is it possible? I had a control box on Page2 that said;
DoCmd.GoToRecord, , acNewRec
Me.Subject = "Job confirmed"
Me.Notes = "These are notes"

... that worked, but I don't know how to make it work if the control box is
on a different Page... and different form.

Thanks again
Scott.

You may have got your code here:
http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html
Is Supervisor both the name of an actual field in your form's Record Source
and the name of an unbound combo box? (It's usually best to avoid calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular item in
an unbound list or combo box using the order that the item appears in - the
first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound one. Is it?

I suspect strongly that there are things that you haven't told us. For
instance, are you trying to filter an unbound form? What is the purpose of
the Unbound combo? Are you actually trying to filter a form at all or are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo then the
method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it is based on
the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and having
BuildID BuilderName, SupervisorID from the Builders table.
Under SupervisorID, in the Criteria Row, you would type
Forms![TheNameOfYourForm].[cboBuilder]

The Row Source of cboBuilder would be this query

cboSupervisor's AfterUpdate Event will have
Me.cboBuilder.Requery.
Me.cboBuilder.Value = Null
'sets what you see in cboBuilder to Null so that you don't have the value
'from the last filter showing

Evi




you would then open the Properties box of cboBuilder and next to Row Source
you would type

Evi,

Thanks a heap for your response... while i was replying to you i found out
what was happening. My filter was showing me all my records (Form1), but
Form2 (which is the form i'm trying to open) has some form of filter on it
that prevents it from opening particular records. Ie. records 3-7

don't
open

but 8 does etc. etc.

Now i believe the reason for particular records not being opened is

with
my

code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub
---------------------------

Now this code is to allow for two a sycronised combo boxes (ie. limit what
can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i basically
copied it out of one of these newsgroup so i don't understand it fully.

Thanks a heap again.





:

I've read this a couple of times and it still isn't making any

sense.
See if

this is what is happening.
You have a continuous form - Form 1.
Each record has a button. If you click a button you want to open another
form, Form 2, filtered so that you only show the details for the record
whose button you have clicked.
OK?
Your code is very straight forward and that ought to work *unless*

Job
No is

a text field.

You seem to be saying that it works for the first 2 records in Form

1,
ie

Form 2 opens correctly filtered, so I must assume that Job No is a number
field or it wouldn't work at all.
Then you say that Form 2, opens and shows no records.

Obviously, if you had New Record set on Form 2 then that would stop your
filter from working so you correctly deleted that.
What code do you have in Form 2? Open its code module and (if necessary)
paste any code here which you do not entirely understand.
Is there any data in Form 2 for the other records which open as

blanks
when

you use Open Form

Check Form2's Properties and see if there is still some Filter or

Order
By

line that refers to something that no longer exists - this is unlikely
because the correct filtering of the first 2 records should have sorted
that.

Evi





Okay, i've spent half a day going through all the different

question
here

in
the newsgroups to try and find a solution but have had no luck.

I need to open a form, but on a specific record. I have a control button
that has the follow commond onClick;

DoCmd.OpenForm "Job1", , , "[Job No]=" & Me.[Job No]

No... the curious problem is this... The first form is a continious,
showing
all records based on a filter. I think have this "Open Form" button on
each
form.

When i click it on the first two lines, they seem to open fine,

but
from

there on... it seems to open a new record.

I used to have a "NewRecord" set at the onOpen, but got rid of that
because
it obvoiusly caused me grief when trying to open a specific record...
could
there be somewhere that is still floating around?

Is there a better OpenForm command to use? I've tried the one

through
the

Controls Wizard and that does much the same thing.

 

[Skot]

Sorry for my late response Evi... i've been struggling away....

I think we're close to what we need to do, but I didn't quite explain what i
needed to do very well...

So we have two tables on seperate tab pages, both linked by a common [Job
No] foreign key.

The user will fill in the information on the first page, (Table1) and click
a button called confirm. This will save the info (got the if Me.Dirty part),
and then start a new record in a new table (Table2), adding, Date, Time,
Subject and Notes into that table, save that table and the user is none the
wiser.

If it helps, the Table1 is the details of a job, Table2 is a running log of
everything that happens, so basically everytime someone does something in
Table1, we get a description of it in Table2. The user can add their own info
into that table as well. That way I can print a report of Table2 and it gives
me a history of that job.

I thank you again for your help Evi. Please let me know if I haven't
explained myself properly.

If your combo box is bound then you can't set its value to null (bound means
that if you changed the BuilderID field in your table, your combo box's
display would automatically change from bill smith to george brown and if
you changed the combo so that a different name from Bill Smith appeared then
the ID field in your table would change) That code you have used is for an
Unbound combo. You can put an unbound combo into your form. Base it on your
builders table but when it comes to the bit in the Wizard where is says

Your tab question: It's not the controls on page 2 that you will need to
fill in, but the table on which page 2 is based.
Is Page 2 based on a different table from Page 1?

If yes, then the basic idea is that when you have finished adding a new
record to Page 1 (After Insert Event), an Append Query runs which adds
something
to the table on which Page 2 is built.
Presumably Page 2 is a linked table which contains a foreign key field which
is the Primary field of Page 1's table.

The code in the After Insert Event would look something like this (depending
on your field and table names.

Dim MyField1 As Long
Dim MyField1Name As String
Dim MySql As String
Dim MyOtherTable As String

MyOtherTable = "The Name of your table"
MyField1Name = "TheNameOfYourNumberField"
'edit as suits

If Me.Dirty = True Then
Me.Dirty = False
'save the current record.

MyField1 = Me.TheNameOfTheLinkingField

MySql = "INSERT INTO " & MyOtherTable & " ( " & MyFieldName & " )" & "
VALUES ( " & MyField1 & ");"
DoCmd.RunSql MySql

If the values don't show up when you tab to page 2 then use
Me.Form.Requery in the On Click event of the page.



Evi

Evi,

Thanks again for ur response... I really appreciate it.

My combo box is actually bound (well, i think it is)..

Anyway, I've had a bit of a look at the code again and tried a few of your
suggestions. When I change the line to; "Me.cboBuilder.Value = Null" i get an
error message; "You tried to assign the Null value to a variable that is not
a Variant data type". I've got rid of the onLoad procedure and checked to
make sure my query matches what you wrote but it still doesn't work. I'm not
overly concerned because I realised that the only reason why I have blank
fields in the Supervisor section is just for debugging... and i can probably
just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for you...
I have a TAB control on a page. I want to add a control Box on say "Page1"
that will create a new record on "Page2" (subform called "Form2"), fill in
the fields and save it... without leaving "Page1".

Do I make sense and is it possible? I had a control box on Page2 that said;
DoCmd.GoToRecord, , acNewRec
Me.Subject = "Job confirmed"
Me.Notes = "These are notes"

... that worked, but I don't know how to make it work if the control box is
on a different Page... and different form.

Thanks again
Scott.

You may have got your code here:
http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html
Is Supervisor both the name of an actual field in your form's Record Source
and the name of an unbound combo box? (It's usually best to avoid calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular item in
an unbound list or combo box using the order that the item appears in - the
first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound one. Is it?

I suspect strongly that there are things that you haven't told us. For
instance, are you trying to filter an unbound form? What is the purpose of
the Unbound combo? Are you actually trying to filter a form at all or are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo then the
method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it is based on
the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and having
BuildID BuilderName, SupervisorID from the Builders table.
Under SupervisorID, in the Criteria Row, you would type
Forms![TheNameOfYourForm].[cboBuilder]

The Row Source of cboBuilder would be this query

cboSupervisor's AfterUpdate Event will have
Me.cboBuilder.Requery.
Me.cboBuilder.Value = Null
'sets what you see in cboBuilder to Null so that you don't have the value
'from the last filter showing

Evi




you would then open the Properties box of cboBuilder and next to Row Source
you would type




Evi,

Thanks a heap for your response... while i was replying to you i found out
what was happening. My filter was showing me all my records (Form1), but
Form2 (which is the form i'm trying to open) has some form of filter on it
that prevents it from opening particular records. Ie. records 3-7 don't
open
but 8 does etc. etc.

Now i believe the reason for particular records not being opened is with
my
code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub
---------------------------

Now this code is to allow for two a sycronised combo boxes (ie. limit what
can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i
basically
copied it out of one of these newsgroup so i don't understand it fully.

Thanks a heap again.





:

I've read this a couple of times and it still isn't making any sense.
See if
this is what is happening.
You have a continuous form - Form 1.
Each record has a button. If you click a button you want to open another
form, Form 2, filtered so that you only show the details for the record
whose button you have clicked.
OK?
Your code is very straight forward and that ought to work *unless* Job
No is
a text field.

You seem to be saying that it works for the first 2 records in Form 1,
ie
Form 2 opens correctly filtered, so I must assume that Job No is a
number
field or it wouldn't work at all.
Then you say that Form 2, opens and shows no records.

Obviously, if you had New Record set on Form 2 then that would stop your
filter from working so you correctly deleted that.
What code do you have in Form 2? Open its code module and (if necessary)
paste any code here which you do not entirely understand.
Is there any data in Form 2 for the other records which open as blanks
when
you use Open Form

Check Form2's Properties and see if there is still some Filter or Order
By
line that refers to something that no longer exists - this is unlikely
because the correct filtering of the first 2 records should have sorted
that.

Evi





Okay, i've spent half a day going through all the different question
here
in
the newsgroups to try and find a solution but have had no luck.

I need to open a form, but on a specific record. I have a control
button
that has the follow commond onClick;

DoCmd.OpenForm "Job1", , , "[Job No]=" & Me.[Job No]

No... the curious problem is this... The first form is a continious,
showing
all records based on a filter. I think have this "Open Form" button on
each
form.

When i click it on the first two lines, they seem to open fine, but
from
there on... it seems to open a new record.

I used to have a "NewRecord" set at the onOpen, but got rid of that
because
it obvoiusly caused me grief when trying to open a specific record...
could
there be somewhere that is still floating around?

 

[Evi]

Oh, I see. So (I hope). You have TblJob with something like
JobNo ( Primary Key) (1 record for each job)
Job

TblJobDetails (several records for each job)
JobDetailID (PK)
JobNo (foreign key, linked from TblJob)
Details about that particular job detail

So TblJobDetails is clearly a subform of TblJob

We have probably answered this before but this thread has got a bit
convoluted.
On which table is the Main form based?
If it is based on a third table, what is the table's name, what field links
it to TblJob?
Or is the main form an unbound form?
Evi

Sorry for my late response Evi... i've been struggling away....

I think we're close to what we need to do, but I didn't quite explain what i
needed to do very well...

So we have two tables on seperate tab pages, both linked by a common [Job
No] foreign key.

The user will fill in the information on the first page, (Table1) and click
a button called confirm. This will save the info (got the if Me.Dirty part),
and then start a new record in a new table (Table2), adding, Date, Time,
Subject and Notes into that table, save that table and the user is none the
wiser.

If it helps, the Table1 is the details of a job, Table2 is a running log of
everything that happens, so basically everytime someone does something in
Table1, we get a description of it in Table2. The user can add their own info
into that table as well. That way I can print a report of Table2 and it gives
me a history of that job.

I thank you again for your help Evi. Please let me know if I haven't
explained myself properly.

If your combo box is bound then you can't set its value to null (bound means
that if you changed the BuilderID field in your table, your combo box's
display would automatically change from bill smith to george brown and if
you changed the combo so that a different name from Bill Smith appeared then
the ID field in your table would change) That code you have used is for an
Unbound combo. You can put an unbound combo into your form. Base it on your
builders table but when it comes to the bit in the Wizard where is says

Your tab question: It's not the controls on page 2 that you will need to
fill in, but the table on which page 2 is based.
Is Page 2 based on a different table from Page 1?

If yes, then the basic idea is that when you have finished adding a new
record to Page 1 (After Insert Event), an Append Query runs which adds
something
to the table on which Page 2 is built.
Presumably Page 2 is a linked table which contains a foreign key field which
is the Primary field of Page 1's table.

The code in the After Insert Event would look something like this (depending
on your field and table names.

Dim MyField1 As Long
Dim MyField1Name As String
Dim MySql As String
Dim MyOtherTable As String

MyOtherTable = "The Name of your table"
MyField1Name = "TheNameOfYourNumberField"
'edit as suits

If Me.Dirty = True Then
Me.Dirty = False
'save the current record.

MyField1 = Me.TheNameOfTheLinkingField

MySql = "INSERT INTO " & MyOtherTable & " ( " & MyFieldName & " )" & "
VALUES ( " & MyField1 & ");"
DoCmd.RunSql MySql

If the values don't show up when you tab to page 2 then use
Me.Form.Requery in the On Click event of the page.



Evi

Evi,

Thanks again for ur response... I really appreciate it.

My combo box is actually bound (well, i think it is)..

Anyway, I've had a bit of a look at the code again and tried a few of your
suggestions. When I change the line to; "Me.cboBuilder.Value = Null" i

get
an

error message; "You tried to assign the Null value to a variable that

is
not

a Variant data type". I've got rid of the onLoad procedure and checked to
make sure my query matches what you wrote but it still doesn't work.

I'm
not

overly concerned because I realised that the only reason why I have blank
fields in the Supervisor section is just for debugging... and i can probably
just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for you...
I have a TAB control on a page. I want to add a control Box on say "Page1"
that will create a new record on "Page2" (subform called "Form2"), fill in
the fields and save it... without leaving "Page1".

Do I make sense and is it possible? I had a control box on Page2 that said;
DoCmd.GoToRecord, , acNewRec
Me.Subject = "Job confirmed"
Me.Notes = "These are notes"

... that worked, but I don't know how to make it work if the control

box
is

on a different Page... and different form.

Thanks again
Scott.



:

You may have got your code here:

http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html

Is Supervisor both the name of an actual field in your form's Record Source
and the name of an unbound combo box? (It's usually best to avoid calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular item in
an unbound list or combo box using the order that the item appears

in -
the

first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound one. Is it?

I suspect strongly that there are things that you haven't told us. For
instance, are you trying to filter an unbound form? What is the

purpose
of

the Unbound combo? Are you actually trying to filter a form at all

or
are

you trying to filter a combo?

If by any chance you want to filter a combo using another combo

then
the

method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it is

based
on

the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and having
BuildID BuilderName, SupervisorID from the Builders table.
Under SupervisorID, in the Criteria Row, you would type
Forms![TheNameOfYourForm].[cboBuilder]

The Row Source of cboBuilder would be this query

cboSupervisor's AfterUpdate Event will have
Me.cboBuilder.Requery.
Me.cboBuilder.Value = Null
'sets what you see in cboBuilder to Null so that you don't have the value
'from the last filter showing

Evi




you would then open the Properties box of cboBuilder and next to Row Source
you would type




Evi,

Thanks a heap for your response... while i was replying to you i

found
out

what was happening. My filter was showing me all my records

(Form1),
but

Form2 (which is the form i'm trying to open) has some form of

filter
on it

that prevents it from opening particular records. Ie. records 3-7 don't
open
but 8 does etc. etc.

Now i believe the reason for particular records not being opened

is
with

my
code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub

limit
what

can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i
basically
copied it out of one of these newsgroup so i don't understand it fully.

Thanks a heap again.





:

I've read this a couple of times and it still isn't making any sense.
See if
this is what is happening.
You have a continuous form - Form 1.
Each record has a button. If you click a button you want to open another
form, Form 2, filtered so that you only show the details for

the
record

whose button you have clicked.
OK?
Your code is very straight forward and that ought to work

*unless*
Job

No is
a text field.

You seem to be saying that it works for the first 2 records in

Form
1,

ie
Form 2 opens correctly filtered, so I must assume that Job No is a
number
field or it wouldn't work at all.
Then you say that Form 2, opens and shows no records.

Obviously, if you had New Record set on Form 2 then that would

stop
your

filter from working so you correctly deleted that.
What code do you have in Form 2? Open its code module and (if necessary)
paste any code here which you do not entirely understand.
Is there any data in Form 2 for the other records which open as blanks
when
you use Open Form

Check Form2's Properties and see if there is still some Filter

or
Order

By
line that refers to something that no longer exists - this is unlikely
because the correct filtering of the first 2 records should have sorted
that.

Evi





Okay, i've spent half a day going through all the different question
here
in
the newsgroups to try and find a solution but have had no luck.

I need to open a form, but on a specific record. I have a control
button
that has the follow commond onClick;

DoCmd.OpenForm "Job1", , , "[Job No]=" & Me.[Job No]

No... the curious problem is this... The first form is a continious,
showing
all records based on a filter. I think have this "Open Form" button on
each
form.

When i click it on the first two lines, they seem to open

fine,
but

from
there on... it seems to open a new record.

I used to have a "NewRecord" set at the onOpen, but got rid of that
because
it obvoiusly caused me grief when trying to open a specific record...
could
there be somewhere that is still floating around?

 

[Skot]

Yep... i think we're getting there. So TblJob has a main form (FrmJob), and
inside it there are two subforms, both with their own tables associated to
them. Lets call them TblJobDetails and TblJobHistory. Both of these run
subforms in the main form off tabs. So they would be FrmJobDetails and
FrmJobHistory.

Its when we click a control on FrmJobDetails that we want to add a new
record into FrmJobHistory, fill it in, and save it.

I've got to the stage where i can open a "hidden" form and start a new
record... but its the filling in part that i'm struggling with.

The link between all the forms are that the TblJob has the [Job No] field
which is the link to TblJobDetails and TblJobHistory.

Oh, I see. So (I hope). You have TblJob with something like
JobNo ( Primary Key) (1 record for each job)
Job

TblJobDetails (several records for each job)
JobDetailID (PK)
JobNo (foreign key, linked from TblJob)
Details about that particular job detail

So TblJobDetails is clearly a subform of TblJob

We have probably answered this before but this thread has got a bit
convoluted.
On which table is the Main form based?
If it is based on a third table, what is the table's name, what field links
it to TblJob?
Or is the main form an unbound form?
Evi

Sorry for my late response Evi... i've been struggling away....

I think we're close to what we need to do, but I didn't quite explain what i
needed to do very well...

So we have two tables on seperate tab pages, both linked by a common [Job
No] foreign key.

The user will fill in the information on the first page, (Table1) and click
a button called confirm. This will save the info (got the if Me.Dirty part),
and then start a new record in a new table (Table2), adding, Date, Time,
Subject and Notes into that table, save that table and the user is none the
wiser.

If it helps, the Table1 is the details of a job, Table2 is a running log of
everything that happens, so basically everytime someone does something in
Table1, we get a description of it in Table2. The user can add their own info
into that table as well. That way I can print a report of Table2 and it gives
me a history of that job.

I thank you again for your help Evi. Please let me know if I haven't
explained myself properly.

If your combo box is bound then you can't set its value to null (bound means
that if you changed the BuilderID field in your table, your combo box's
display would automatically change from bill smith to george brown and if
you changed the combo so that a different name from Bill Smith appeared then
the ID field in your table would change) That code you have used is for an
Unbound combo. You can put an unbound combo into your form. Base it on your
builders table but when it comes to the bit in the Wizard where is says

Your tab question: It's not the controls on page 2 that you will need to
fill in, but the table on which page 2 is based.
Is Page 2 based on a different table from Page 1?

If yes, then the basic idea is that when you have finished adding a new
record to Page 1 (After Insert Event), an Append Query runs which adds
something
to the table on which Page 2 is built.
Presumably Page 2 is a linked table which contains a foreign key field which
is the Primary field of Page 1's table.

The code in the After Insert Event would look something like this (depending
on your field and table names.

Dim MyField1 As Long
Dim MyField1Name As String
Dim MySql As String
Dim MyOtherTable As String

MyOtherTable = "The Name of your table"
MyField1Name = "TheNameOfYourNumberField"
'edit as suits

If Me.Dirty = True Then
Me.Dirty = False
'save the current record.

MyField1 = Me.TheNameOfTheLinkingField

MySql = "INSERT INTO " & MyOtherTable & " ( " & MyFieldName & " )" & "
VALUES ( " & MyField1 & ");"
DoCmd.RunSql MySql

If the values don't show up when you tab to page 2 then use
Me.Form.Requery in the On Click event of the page.



Evi


Evi,

Thanks again for ur response... I really appreciate it.

My combo box is actually bound (well, i think it is)..

Anyway, I've had a bit of a look at the code again and tried a few of your
suggestions. When I change the line to; "Me.cboBuilder.Value = Null" i get
an
error message; "You tried to assign the Null value to a variable that is
not
a Variant data type". I've got rid of the onLoad procedure and checked to
make sure my query matches what you wrote but it still doesn't work. I'm
not
overly concerned because I realised that the only reason why I have blank
fields in the Supervisor section is just for debugging... and i can
probably
just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for
you...
I have a TAB control on a page. I want to add a control Box on say "Page1"
that will create a new record on "Page2" (subform called "Form2"), fill in
the fields and save it... without leaving "Page1".

Do I make sense and is it possible? I had a control box on Page2 that
said;
DoCmd.GoToRecord, , acNewRec
Me.Subject = "Job confirmed"
Me.Notes = "These are notes"

... that worked, but I don't know how to make it work if the control box
is
on a different Page... and different form.

Thanks again
Scott.



:

You may have got your code here:

http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html
Is Supervisor both the name of an actual field in your form's Record
Source
and the name of an unbound combo box? (It's usually best to avoid
calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular
item in
an unbound list or combo box using the order that the item appears in -
the
first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound one. Is it?

I suspect strongly that there are things that you haven't told us. For
instance, are you trying to filter an unbound form? What is the purpose
of
the Unbound combo? Are you actually trying to filter a form at all or
are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo then
the
method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it is based
on
the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and
having
BuildID BuilderName, SupervisorID from the Builders table.
Under SupervisorID, in the Criteria Row, you would type
Forms![TheNameOfYourForm].[cboBuilder]

The Row Source of cboBuilder would be this query

cboSupervisor's AfterUpdate Event will have
Me.cboBuilder.Requery.
Me.cboBuilder.Value = Null
'sets what you see in cboBuilder to Null so that you don't have the
value
'from the last filter showing

Evi




you would then open the Properties box of cboBuilder and next to Row
Source
you would type




Evi,

Thanks a heap for your response... while i was replying to you i found
out
what was happening. My filter was showing me all my records (Form1),
but
Form2 (which is the form i'm trying to open) has some form of filter
on it
that prevents it from opening particular records. Ie. records 3-7
don't
open
but 8 does etc. etc.

Now i believe the reason for particular records not being opened is
with
my
code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub
---------------------------

Now this code is to allow for two a sycronised combo boxes (ie. limit
what
can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the
Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i
basically
copied it out of one of these newsgroup so i don't understand it
fully.

Thanks a heap again.





:

I've read this a couple of times and it still isn't making any
sense.

 

[Evi]

Assuming Job No is a number field. In the OnClick Event of the button you
can put in the following code


Dim MySql As String
Dim MyJobNo As Long
MyJobNo = Me.[Job No]

'the line starting MySql should be one long line
MySql = "INSERT INTO TblJobHistory ( [Job No] ) SELECT " & MyJobNo & " AS
[Job No];"

'MsgBox MySQL
'run the append query

DoCmd.RunSql "MySql
'now requery the other subform
Me.Parent.FrmJobHistory.Requery

'end of code

If the button is actually on the Main Form then this will not work. Just
change the MyJobNoline then to

MyJobNo = Me.YourSubformName.Form.[Job No]

Evi

Yep... i think we're getting there. So TblJob has a main form (FrmJob), and
inside it there are two subforms, both with their own tables associated to
them. Lets call them TblJobDetails and TblJobHistory. Both of these run
subforms in the main form off tabs. So they would be FrmJobDetails and
FrmJobHistory.

Its when we click a control on FrmJobDetails that we want to add a new
record into FrmJobHistory, fill it in, and save it.

I've got to the stage where i can open a "hidden" form and start a new
record... but its the filling in part that i'm struggling with.

The link between all the forms are that the TblJob has the [Job No] field
which is the link to TblJobDetails and TblJobHistory.

Oh, I see. So (I hope). You have TblJob with something like
JobNo ( Primary Key) (1 record for each job)
Job

TblJobDetails (several records for each job)
JobDetailID (PK)
JobNo (foreign key, linked from TblJob)
Details about that particular job detail

So TblJobDetails is clearly a subform of TblJob

We have probably answered this before but this thread has got a bit
convoluted.
On which table is the Main form based?
If it is based on a third table, what is the table's name, what field links
it to TblJob?
Or is the main form an unbound form?
Evi

Sorry for my late response Evi... i've been struggling away....

I think we're close to what we need to do, but I didn't quite explain

what
i

needed to do very well...

So we have two tables on seperate tab pages, both linked by a common [Job
No] foreign key.

The user will fill in the information on the first page, (Table1) and click
a button called confirm. This will save the info (got the if Me.Dirty part),
and then start a new record in a new table (Table2), adding, Date, Time,
Subject and Notes into that table, save that table and the user is

none
the

wiser.

If it helps, the Table1 is the details of a job, Table2 is a running

log
of

everything that happens, so basically everytime someone does something in
Table1, we get a description of it in Table2. The user can add their

own
info

into that table as well. That way I can print a report of Table2 and

it
gives

me a history of that job.

I thank you again for your help Evi. Please let me know if I haven't
explained myself properly.

:

If your combo box is bound then you can't set its value to null

(bound
means

that if you changed the BuilderID field in your table, your combo box's
display would automatically change from bill smith to george brown

and
if

you changed the combo so that a different name from Bill Smith

appeared
then

the ID field in your table would change) That code you have used is

for
an

Unbound combo. You can put an unbound combo into your form. Base it

on
your

builders table but when it comes to the bit in the Wizard where is says

Your tab question: It's not the controls on page 2 that you will need to
fill in, but the table on which page 2 is based.
Is Page 2 based on a different table from Page 1?

If yes, then the basic idea is that when you have finished adding a new
record to Page 1 (After Insert Event), an Append Query runs which adds
something
to the table on which Page 2 is built.
Presumably Page 2 is a linked table which contains a foreign key

field
which

is the Primary field of Page 1's table.

The code in the After Insert Event would look something like this (depending
on your field and table names.

Dim MyField1 As Long
Dim MyField1Name As String
Dim MySql As String
Dim MyOtherTable As String

MyOtherTable = "The Name of your table"
MyField1Name = "TheNameOfYourNumberField"
'edit as suits

If Me.Dirty = True Then
Me.Dirty = False
'save the current record.

MyField1 = Me.TheNameOfTheLinkingField

MySql = "INSERT INTO " & MyOtherTable & " ( " & MyFieldName & " )" & "
VALUES ( " & MyField1 & ");"
DoCmd.RunSql MySql

If the values don't show up when you tab to page 2 then use
Me.Form.Requery in the On Click event of the page.



Evi


Evi,

Thanks again for ur response... I really appreciate it.

My combo box is actually bound (well, i think it is)..

Anyway, I've had a bit of a look at the code again and tried a few

of
your

suggestions. When I change the line to; "Me.cboBuilder.Value =

Null" i
get

an
error message; "You tried to assign the Null value to a variable

that
is

not
a Variant data type". I've got rid of the onLoad procedure and

checked
to

make sure my query matches what you wrote but it still doesn't

work.
I'm

not
overly concerned because I realised that the only reason why I

have
blank

fields in the Supervisor section is just for debugging... and i can
probably
just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for
you...
I have a TAB control on a page. I want to add a control Box on say "Page1"
that will create a new record on "Page2" (subform called "Form2"), fill in
the fields and save it... without leaving "Page1".

Do I make sense and is it possible? I had a control box on Page2 that
said;
DoCmd.GoToRecord, , acNewRec
Me.Subject = "Job confirmed"
Me.Notes = "These are notes"

... that worked, but I don't know how to make it work if the

control
box

is
on a different Page... and different form.

Thanks again
Scott.



:

You may have got your code here:

http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html

Is Supervisor both the name of an actual field in your form's Record
Source
and the name of an unbound combo box? (It's usually best to avoid
calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular
item in
an unbound list or combo box using the order that the item

appears
in -

the
first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound

one. Is
it?

I suspect strongly that there are things that you haven't told

us.
For

instance, are you trying to filter an unbound form? What is the purpose
of
the Unbound combo? Are you actually trying to filter a form at

all
or

are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo then
the
method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it

is
based

on
the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and
having
BuildID BuilderName, SupervisorID from the Builders table.
Under SupervisorID, in the Criteria Row, you would type
Forms![TheNameOfYourForm].[cboBuilder]

The Row Source of cboBuilder would be this query

cboSupervisor's AfterUpdate Event will have
Me.cboBuilder.Requery.
Me.cboBuilder.Value = Null
'sets what you see in cboBuilder to Null so that you don't have the
value
'from the last filter showing

Evi




you would then open the Properties box of cboBuilder and next to Row
Source
you would type




Evi,

Thanks a heap for your response... while i was replying to you

i
found

out
what was happening. My filter was showing me all my records (Form1),
but
Form2 (which is the form i'm trying to open) has some form of filter
on it
that prevents it from opening particular records. Ie. records 3-7
don't
open
but 8 does etc. etc.

Now i believe the reason for particular records not being

opened
is

with
my
code relating to Form2;

--------------------------------------------
Private Sub Builder_AfterUpdate()
Me.Supervisor.Requery
Me.Supervisor = Me.Supervisor.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Supervisor.Requery
End Sub

Private Sub Form_Load()
If IsNull(Builder) Then
Me.Builder = Me.Builder.ItemData(0)
Call Builder_AfterUpdate
End If
End Sub

(ie.
limit

what
can be selected in ComboBox2 based on the slection on ComboBox1).

The items that aren't open are all items that have nothing in the
Combox2
(which in my code above is the SUPERVISOR field)

So.... can u see any problems with my code above... to be honest i
basically
copied it out of one of these newsgroup so i don't understand it
fully.

Thanks a heap again.





:

I've read this a couple of times and it still isn't making any
sense.

 

[Skot]

thanks evi, that looks like it makes sense to me, i'll try and key it tonight
and see how i go.

My only question, in my TblJobHistory, i have two other fields, being say;
[Subject] and [Notes].

While i'm creating the new record, i want to fill in these two fields.

Would i use the INSERT INTO command for that one as well?

Thanks
Scott.
(p.s.) the delay in most of my responses is because i'm here in australia,
and only get a chance to do the work in the evenings.

Assuming Job No is a number field. In the OnClick Event of the button you
can put in the following code


Dim MySql As String
Dim MyJobNo As Long
MyJobNo = Me.[Job No]

'the line starting MySql should be one long line
MySql = "INSERT INTO TblJobHistory ( [Job No] ) SELECT " & MyJobNo & " AS
[Job No];"

'MsgBox MySQL
'run the append query

DoCmd.RunSql "MySql
'now requery the other subform
Me.Parent.FrmJobHistory.Requery

'end of code

If the button is actually on the Main Form then this will not work. Just
change the MyJobNoline then to

MyJobNo = Me.YourSubformName.Form.[Job No]

Evi

Yep... i think we're getting there. So TblJob has a main form (FrmJob), and
inside it there are two subforms, both with their own tables associated to
them. Lets call them TblJobDetails and TblJobHistory. Both of these run
subforms in the main form off tabs. So they would be FrmJobDetails and
FrmJobHistory.

Its when we click a control on FrmJobDetails that we want to add a new
record into FrmJobHistory, fill it in, and save it.

I've got to the stage where i can open a "hidden" form and start a new
record... but its the filling in part that i'm struggling with.

The link between all the forms are that the TblJob has the [Job No] field
which is the link to TblJobDetails and TblJobHistory.

Oh, I see. So (I hope). You have TblJob with something like
JobNo ( Primary Key) (1 record for each job)
Job

TblJobDetails (several records for each job)
JobDetailID (PK)
JobNo (foreign key, linked from TblJob)
Details about that particular job detail

So TblJobDetails is clearly a subform of TblJob

We have probably answered this before but this thread has got a bit
convoluted.
On which table is the Main form based?
If it is based on a third table, what is the table's name, what field links
it to TblJob?
Or is the main form an unbound form?
Evi

Sorry for my late response Evi... i've been struggling away....

I think we're close to what we need to do, but I didn't quite explain what
i
needed to do very well...

So we have two tables on seperate tab pages, both linked by a common [Job
No] foreign key.

The user will fill in the information on the first page, (Table1) and
click
a button called confirm. This will save the info (got the if Me.Dirty
part),
and then start a new record in a new table (Table2), adding, Date, Time,
Subject and Notes into that table, save that table and the user is none
the
wiser.

If it helps, the Table1 is the details of a job, Table2 is a running log
of
everything that happens, so basically everytime someone does something in
Table1, we get a description of it in Table2. The user can add their own
info
into that table as well. That way I can print a report of Table2 and it
gives
me a history of that job.

I thank you again for your help Evi. Please let me know if I haven't
explained myself properly.

:

If your combo box is bound then you can't set its value to null (bound
means
that if you changed the BuilderID field in your table, your combo box's
display would automatically change from bill smith to george brown and
if
you changed the combo so that a different name from Bill Smith appeared
then
the ID field in your table would change) That code you have used is for
an
Unbound combo. You can put an unbound combo into your form. Base it on
your
builders table but when it comes to the bit in the Wizard where is says

Your tab question: It's not the controls on page 2 that you will need to
fill in, but the table on which page 2 is based.
Is Page 2 based on a different table from Page 1?

If yes, then the basic idea is that when you have finished adding a new
record to Page 1 (After Insert Event), an Append Query runs which adds
something
to the table on which Page 2 is built.
Presumably Page 2 is a linked table which contains a foreign key field
which
is the Primary field of Page 1's table.

The code in the After Insert Event would look something like this
(depending
on your field and table names.

Dim MyField1 As Long
Dim MyField1Name As String
Dim MySql As String
Dim MyOtherTable As String

MyOtherTable = "The Name of your table"
MyField1Name = "TheNameOfYourNumberField"
'edit as suits

If Me.Dirty = True Then
Me.Dirty = False
'save the current record.

MyField1 = Me.TheNameOfTheLinkingField

MySql = "INSERT INTO " & MyOtherTable & " ( " & MyFieldName & " )" & "
VALUES ( " & MyField1 & ");"
DoCmd.RunSql MySql

If the values don't show up when you tab to page 2 then use
Me.Form.Requery in the On Click event of the page.



Evi


Evi,

Thanks again for ur response... I really appreciate it.

My combo box is actually bound (well, i think it is)..

Anyway, I've had a bit of a look at the code again and tried a few of
your
suggestions. When I change the line to; "Me.cboBuilder.Value = Null" i
get
an
error message; "You tried to assign the Null value to a variable that
is
not
a Variant data type". I've got rid of the onLoad procedure and checked
to
make sure my query matches what you wrote but it still doesn't work.
I'm
not
overly concerned because I realised that the only reason why I have
blank
fields in the Supervisor section is just for debugging... and i can
probably
just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for
you...
I have a TAB control on a page. I want to add a control Box on say
"Page1"
that will create a new record on "Page2" (subform called "Form2"),
fill in
the fields and save it... without leaving "Page1".

Do I make sense and is it possible? I had a control box on Page2 that
said;
DoCmd.GoToRecord, , acNewRec
Me.Subject = "Job confirmed"
Me.Notes = "These are notes"

... that worked, but I don't know how to make it work if the control
box
is
on a different Page... and different form.

Thanks again
Scott.



:

You may have got your code here:


http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html
Is Supervisor both the name of an actual field in your form's Record
Source
and the name of an unbound combo box? (It's usually best to avoid
calling
Controls by a field name - Access will get revenge on you!).

I ask because ItemData (number) is usually used to find a particular
item in
an unbound list or combo box using the order that the item appears
in -
the
first item is always 0, the second item in the list is always 1.
The code is used so that the user, instead of being faced with a
blank
unbound combo when he opens his form, is faced with one that already
contains the first line of data (are you sure that you want this?)
I can't imagine what it would do if the combo box is a bound one. Is
it?

I suspect strongly that there are things that you haven't told us.
For
instance, are you trying to filter an unbound form? What is the
purpose
of
the Unbound combo? Are you actually trying to filter a form at all
or
are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo
then
the
method is slightly different.
This may give you the idea.
Your first combo, cboSupervisor has a list of Supervisors - it is
based
on
the TblSupervisor table and has SupervisorId (Primary key) and
SupervisorName
cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table

and

 

[Evi]

Hi, Skot







Where would the information come from to fill in those fields? They don't

sound like something that should be in another table for you to 'copy and

paste' across with an append query



I thought that they would be something on page 2 of your tab, in the
FrmJobHistory Subform



One important question.

Does your TblJobDetails have one record per job and just provides further
details about the job?

Does your TblJobHistory have several records per job



In the meantime, here is how I'm using a form with 2 subforms in tab
controls. You may be able to adapt some of the ideas eg how to go to the
last record of the second subform and how to ensure that a newly added
record is saved

The lines starting with apostrophes won't run as code - they are comments

In my case, the second subform is not linked to the main form but to the
first subform. You'll see that the tab control is actually ignored in the
code - when you set focus on the second subform this automatically 'turns
the page'



Main Form FrmSales has the fields Manufacturer and ManID (primary key of
TblManufacturer



Tab is TabSales

PgProduct has FrmSalesSub1 (which is linked to main form via ManID)

It lists the products for sale and has the fields

ProdID, Product and RRPrice (recommended price for product)



PgSale contains FrmSalesSub2 (a list of sold products including ProdID and
SalePrice)

It's child link is ProdID

MasterLink to the other subform is

[FrmSalesSub1].[Form]![ProdID]

(the syntax must be carefully observed)

I have a button on PgProduct which says Buy and lets me click on a product
and buy it



Private Sub cmbBuy_Click()
On Error GoTo Err_cmbBuy_Click



Dim stDocName As String

Dim MyProd As Long

Dim MyPrice As Double

Dim MySql As String

Dim MyMsg As String



Dim ProductName As String



If Me.FrmSalesSub1.Form.Dirty = True Then

'save record if a new product has just been added

'and writer hasn't clicked off record yet

Me.FrmSalesSub1.Form.Dirty = False

End If



Me.FrmSalesSub1.Requery





If IsNull(Me.[FrmSalesSub1].Form.[ProdID]) Then

'nothing in the subform

MsgBox "No products for this manufacturer"

Exit Sub

End If



'fill variables

ProductName = Me.Manufacturer & " " & Me.[FrmSalesSub1].Form.[Product]

MyPrice = Nz(Me.[FrmSalesSub1].Form.[RRPrice], 0)

MyProd = Me.[FrmSalesSub1].Form.[ProdID]

MySql = "INSERT INTO [TblSale] ( [ProdID], [SalePrice] )"

MySql = MySql & " SELECT " & MyProd & " AS [ProdID], " & MyPrice & " AS
[SalePrice];"

MyMsg = "Buy " & ProductName & "?"



If MsgBox(MyMsg, vbYesNo) = vbNo Then

Exit Sub

Else



DoCmd.RunSQL MySql

'After checking this works as expected

'I can delete the DoCmd

'and uncomment the line below

'CurrentDb.Execute MySql, dbFailOnError

'this won't give the 'Record about to Added' box

Me.FrmSalesSub2.Requery

'ensure newly added record will appear in subform 2

Me.FrmSalesSub2.SetFocus

'Move to second subform

Me.FrmSalesSub2.Form.RecordsetClone.MoveLast

'move to newly inserted record for editing

End If



Exit_cmbBuy_Click:

Exit Sub



Err_cmbBuy_Click:

MsgBox Err.Description

Resume Exit_cmbBuy_Click



End Sub










"Skot" <[email protected] wrote in message


thanks evi, that looks like it makes sense to me, i'll try and key it
tonightand see how i go.



My only question, in my TblJobHistory, i have two other fields, being
say;[Subject] and [Notes].

While i'm creating the new record, i want to fill in these two fields.

Would i use the INSERT INTO command for that one as well?



Evi

Assuming Job No is a number field. In the OnClick Event of the button you
can put in the following code

Dim MySql As String

Dim MyJobNo As Long

MyJobNo = Me.[Job No]



'the line starting MySql should be one long line

MySql = "INSERT INTO TblJobHistory ( [Job No] ) SELECT" & MyJobNo & "

AS

[Job No];"



'MsgBox MySQL

'run the append query



DoCmd.RunSql "MySql

'now requery the other subform

Me.Parent.FrmJobHistory.Requery

'end of code

If the button is actually on the Main Form then this will not work.
Justchange the MyJobNoline then to

MyJobNo = Me.YourSubformName.Form.[Job No]

"Skot" <[email protected] wrote in message

Yep... i think we're getting there. So TblJob has a main form (FrmJob),and
inside it there are two subforms, both with their own tables associated to
them. Lets call them TblJobDetails and TblJobHistory. Both of these run
subforms in the main form off tabs. So they would be FrmJobDetails and
FrmJobHistory.

Its when we click a control on FrmJobDetails that we want to add a new
record into FrmJobHistory, fill it in, and save it.

I've got to the stage where i can open a "hidden" form and start a new
record... but its the filling in part that i'm struggling with. The link
between all the forms are that the TblJob has the [Job No] field which is
the link to TblJobDetails and TblJobHistory.



:

Oh, I see. So (I hope). You have

TblJob with something like J

obNo ( Primary Key) (1 record for each job) Job



TblJobDetails (several records for each job)

JobDetailID (PK)

JobNo (foreign key, linked from TblJob)

Details about that particular job detail



So TblJobDetails is clearly a subform of TblJob



We have probably answered this before but this thread has got a bit
convoluted.On which table is the Main form based?

Ifit is based on a third table, what is the table's name, what field linksit
to TblJob?

Or is the main form an unbound form?

Evi



"Skot" <[email protected] wrote in message

I think we're close to what we need to do, but I didn't quite

explain what i needed to do very well...

So we have two tables on seperate tab pages, both linked by a

common [Job No] foreign key.

The user will fill in the information on the first page, (Table1)

and click a button called confirm. This will save the info (got the if
Me.Dirtypart),

and then start a new record in a new table (Table2), adding,
Date,Time,Subject and Notes into that table, save that table and the user
isnone thewiser. If it helps, the Table1 is the details of a job, Table2 is
a runninglog of

everything that happens, so basically everytime someone does
somethinginTable1, we get a description of it in Table2. The user can add
theirown info into that table as well. That way I can print a report of
Table2 andit givesme a history of that job. I thank you again for your help
Evi. Please let me know if I haven'texplained myself properly.



:

If your combo box is bound then you can't set its value to null(bound
meansthat if you changed the BuilderID field in your table, your
combobox'sdisplay would automatically change from bill smith to george
brownand ifyou changed the combo so that a different name from Bill
Smithappeared then

the ID field in your table would change) That code you have used

isfor anUnbound combo. You can put an unbound combo into your form. Base
iton yourbuilders table but when it comes to the bit in the Wizard where
issays



Your tab question: It's not the controls on page 2 that you willneed tofill
in, but the table on which page 2 is based.Is Page 2 based on a different
table from Page 1?

If yes, then the basic idea is that when you have finished adding anewrecord
to Page 1 (After Insert Event), an Append Query runs whichaddssomethingto
the table on which Page 2 is built.

Presumably Page 2 is a linked table which contains a foreign keyfield
whichis the Primary field of Page 1's table.



The code in the After Insert Event would look something like this

(depending on your field and table names.



Dim MyField1 As Long

Dim MyField1Name As String

Dim MySql As String

Dim MyOtherTable As String



MyOtherTable = "The Name of your table"

MyField1Name = "TheNameOfYourNumberField"

'edit as suits



If Me.Dirty = True Then

Me.Dirty = False

'save the current record.



MyField1 = Me.TheNameOfTheLinkingField



MySql = "INSERT INTO " & MyOtherTable & " ( " & MyFieldName & " )" &

"

VALUES ( " & MyField1 & ");"

DoCmd.RunSql MySql

If the values don't show up when you tab to page 2 then useMe.Form.Requery
in the On Click event of the page.



"Skot" <[email protected] wrote in message

Thanks again for ur response... I really appreciate it. My combo box is
actually bound (well, i think it is).. Anyway, I've had a bit of a look at
the code again and tried a fewof your suggestions. When I change the line
to; "Me.cboBuilder.Value =

Null" i get an error message; "You tried to assign the Null value to a
variable that is not

a Variant data type". I've got rid of the onLoad procedure and checked to
make sure my query matches what you wrote but it still doesn't work. I'm
not overly concerned because I realised that the only reason why I have
blank fields in the Supervisor section is just for debugging... and i can
probably just set a default value for it if it caused too much bother...

BUT... now that I've got you answering queries, I have another one for
you... I have a TAB control on a page. I want to add a control Box on say
"Page1" that will create a new record on "Page2" (subform called "Form2"),
fill in the fields and save it... without leaving "Page1".





DoCmd.GoToRecord, , acNewRec

Me.Subject = "Job confirmed"

Me.Notes = "These are notes"



.... that worked, but I don't know how to make it work if thecontrol boxis on
a different Page... and different form. Thanks again Scott.You may have got
your code here:

http://www.access-programmers.co.uk/forums/archive/index.php/t-94035.html

Is Supervisor both the name of an actual field in your form'sRecordSource
and the name of an unbound combo box? (It's usually best toavoidcalling
Controls by a field name - Access will get revenge on you!). I ask because
ItemData (number) is usually used to find aparticularitem in an unbound list
or combo box using the order that the itemappears in -the first item is
always 0, the second item in the list is always 1. The code is used so that
the user, instead of being faced with a blank unbound combo when he opens
his form, is faced with one thatalready contains the first line of data (are
you sure that you want this?)

I can't imagine what it would do if the combo box is a bound one. Is it?



I suspect strongly that there are things that you haven't told us. For
instance, are you trying to filter an unbound form? What is the purpose of
the Unbound combo? Are you actually trying to filter a form at all or are
you trying to filter a combo?

If by any chance you want to filter a combo using another combo thenthe
method is slightly different. This may give you the idea. Your first combo,
cboSupervisor has a list of Supervisors - itis basedon the TblSupervisor
table and has SupervisorId (Primary key) andSupervisorName

cboBuilder has a list of builders which that Supervisor looks after.

To get its data, you create a query based on TblBuilders table and