Problem passing value after requery into openargs

N

Nikkiewolf

I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
 
C

Cheese_whiz

Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW
 
N

Nikkiewolf

Hi CW,

Thanks for your response.

I am pretty new to Access so apologies for my set up explanation. I'm
probably over complicating things.

With some more playing around, I'm sure the problem is on my first form and
is something to do with the way I am using requery.

My table is 3 columns: Category, Category Label, Category Options. I use
the Category as lookup to populate combo boxes in other forms and tables with
a list of options.

To enable the users to review and add to the Category options I've created a
form with unbound command boxes that query the table as follows:
Combo box (me.combo9) - a select distinct on 'Category Label'
Text box (me.reflist) - Select distinct on 'Category' Where 'Category Label'
= value selected in Combo box
List box (me.list7) - Select on 'Category Options' where 'Category Label' =
value selected in Combo box
To populate the Text box and List box I've requeried the boxes on the Combo
box AfterUpdate.

The Combo box works fine. The other two command boxes populate on the
screen but I can't use the values in them.

Using the following code I get 'Invalid use of null' error when it hits the
second line of code.
***code***
DIM categoryparm string
categoryparm = me.reflist

DoCmd.OpenForm stDocName, , , , , , categoryparm
***end code***

When I try to directly pass me.reflist in the openargs (DoCmd.OpenForm
stDocName, , , , , ,me.reflist) I get 'An expression you entered is the wrong
data type for one of the arguments'.

If this makes any sense at all and you are able to help then great! If not,
just tell me to go back to the drawing board.

Many thanks,
Nikkie


Cheese_whiz said:
Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW



Nikkiewolf said:
I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
Click to expand...
Click to expand...
 
C

Cheese_whiz

Hi Nikkie,

All I was saying is that if a value......or, rather, if what you SEE in a
text box is NOT something that has been committed, and it can't be since your
text box is NOT bound, then I think you need to use the .text property to get
what you SEE in the text box.

So, instead of:

categoryparm = me.reflist

I think you might need to use:

categoryparm = me.reflist.text

I may be wrong but it's about a two second operation to test my theory and
it may help :)

Good luck!
CW

Nikkiewolf said:
Hi CW,

Thanks for your response.

I am pretty new to Access so apologies for my set up explanation. I'm
probably over complicating things.

With some more playing around, I'm sure the problem is on my first form and
is something to do with the way I am using requery.

My table is 3 columns: Category, Category Label, Category Options. I use
the Category as lookup to populate combo boxes in other forms and tables with
a list of options.

To enable the users to review and add to the Category options I've created a
form with unbound command boxes that query the table as follows:
Combo box (me.combo9) - a select distinct on 'Category Label'
Text box (me.reflist) - Select distinct on 'Category' Where 'Category Label'
= value selected in Combo box
List box (me.list7) - Select on 'Category Options' where 'Category Label' =
value selected in Combo box
To populate the Text box and List box I've requeried the boxes on the Combo
box AfterUpdate.

The Combo box works fine. The other two command boxes populate on the
screen but I can't use the values in them.

Using the following code I get 'Invalid use of null' error when it hits the
second line of code.
***code***
DIM categoryparm string
categoryparm = me.reflist

DoCmd.OpenForm stDocName, , , , , , categoryparm
***end code***

When I try to directly pass me.reflist in the openargs (DoCmd.OpenForm
stDocName, , , , , ,me.reflist) I get 'An expression you entered is the wrong
data type for one of the arguments'.

If this makes any sense at all and you are able to help then great! If not,
just tell me to go back to the drawing board.

Many thanks,
Nikkie


Cheese_whiz said:
Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW



Nikkiewolf said:
I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
N

Nikkiewolf

Thanks again CW.

I have tried what you suggested but I get a compile error - 'Method or data
member not found' and the .text is highlighted in the VB debug.

Thanks,
Nikkie


Cheese_whiz said:
Hi Nikkie,

All I was saying is that if a value......or, rather, if what you SEE in a
text box is NOT something that has been committed, and it can't be since your
text box is NOT bound, then I think you need to use the .text property to get
what you SEE in the text box.

So, instead of:

categoryparm = me.reflist

I think you might need to use:

categoryparm = me.reflist.text

I may be wrong but it's about a two second operation to test my theory and
it may help :)

Good luck!
CW

Nikkiewolf said:
Hi CW,

Thanks for your response.

I am pretty new to Access so apologies for my set up explanation. I'm
probably over complicating things.

With some more playing around, I'm sure the problem is on my first form and
is something to do with the way I am using requery.

My table is 3 columns: Category, Category Label, Category Options. I use
the Category as lookup to populate combo boxes in other forms and tables with
a list of options.

To enable the users to review and add to the Category options I've created a
form with unbound command boxes that query the table as follows:
Combo box (me.combo9) - a select distinct on 'Category Label'
Text box (me.reflist) - Select distinct on 'Category' Where 'Category Label'
= value selected in Combo box
List box (me.list7) - Select on 'Category Options' where 'Category Label' =
value selected in Combo box
To populate the Text box and List box I've requeried the boxes on the Combo
box AfterUpdate.

The Combo box works fine. The other two command boxes populate on the
screen but I can't use the values in them.

Using the following code I get 'Invalid use of null' error when it hits the
second line of code.
***code***
DIM categoryparm string
categoryparm = me.reflist

DoCmd.OpenForm stDocName, , , , , , categoryparm
***end code***

When I try to directly pass me.reflist in the openargs (DoCmd.OpenForm
stDocName, , , , , ,me.reflist) I get 'An expression you entered is the wrong
data type for one of the arguments'.

If this makes any sense at all and you are able to help then great! If not,
just tell me to go back to the drawing board.

Many thanks,
Nikkie


Cheese_whiz said:
Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW



:

I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
C

Cheese_whiz

Hi again,

Is reflist not a text box?

CW

Nikkiewolf said:
Thanks again CW.

I have tried what you suggested but I get a compile error - 'Method or data
member not found' and the .text is highlighted in the VB debug.

Thanks,
Nikkie


Cheese_whiz said:
Hi Nikkie,

All I was saying is that if a value......or, rather, if what you SEE in a
text box is NOT something that has been committed, and it can't be since your
text box is NOT bound, then I think you need to use the .text property to get
what you SEE in the text box.

So, instead of:

categoryparm = me.reflist

I think you might need to use:

categoryparm = me.reflist.text

I may be wrong but it's about a two second operation to test my theory and
it may help :)

Good luck!
CW

Nikkiewolf said:
Hi CW,

Thanks for your response.

I am pretty new to Access so apologies for my set up explanation. I'm
probably over complicating things.

With some more playing around, I'm sure the problem is on my first form and
is something to do with the way I am using requery.

My table is 3 columns: Category, Category Label, Category Options. I use
the Category as lookup to populate combo boxes in other forms and tables with
a list of options.

To enable the users to review and add to the Category options I've created a
form with unbound command boxes that query the table as follows:
Combo box (me.combo9) - a select distinct on 'Category Label'
Text box (me.reflist) - Select distinct on 'Category' Where 'Category Label'
= value selected in Combo box
List box (me.list7) - Select on 'Category Options' where 'Category Label' =
value selected in Combo box
To populate the Text box and List box I've requeried the boxes on the Combo
box AfterUpdate.

The Combo box works fine. The other two command boxes populate on the
screen but I can't use the values in them.

Using the following code I get 'Invalid use of null' error when it hits the
second line of code.
***code***
DIM categoryparm string
categoryparm = me.reflist

DoCmd.OpenForm stDocName, , , , , , categoryparm
***end code***

When I try to directly pass me.reflist in the openargs (DoCmd.OpenForm
stDocName, , , , , ,me.reflist) I get 'An expression you entered is the wrong
data type for one of the arguments'.

If this makes any sense at all and you are able to help then great! If not,
just tell me to go back to the drawing board.

Many thanks,
Nikkie


:

Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW



:

I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
N

Nikkiewolf

Sorry, my mistake. It is a list box.

N

Cheese_whiz said:
Hi again,

Is reflist not a text box?

CW

Nikkiewolf said:
Thanks again CW.

I have tried what you suggested but I get a compile error - 'Method or data
member not found' and the .text is highlighted in the VB debug.

Thanks,
Nikkie


Cheese_whiz said:
Hi Nikkie,

All I was saying is that if a value......or, rather, if what you SEE in a
text box is NOT something that has been committed, and it can't be since your
text box is NOT bound, then I think you need to use the .text property to get
what you SEE in the text box.

So, instead of:

categoryparm = me.reflist

I think you might need to use:

categoryparm = me.reflist.text

I may be wrong but it's about a two second operation to test my theory and
it may help :)

Good luck!
CW

:

Hi CW,

Thanks for your response.

I am pretty new to Access so apologies for my set up explanation. I'm
probably over complicating things.

With some more playing around, I'm sure the problem is on my first form and
is something to do with the way I am using requery.

My table is 3 columns: Category, Category Label, Category Options. I use
the Category as lookup to populate combo boxes in other forms and tables with
a list of options.

To enable the users to review and add to the Category options I've created a
form with unbound command boxes that query the table as follows:
Combo box (me.combo9) - a select distinct on 'Category Label'
Text box (me.reflist) - Select distinct on 'Category' Where 'Category Label'
= value selected in Combo box
List box (me.list7) - Select on 'Category Options' where 'Category Label' =
value selected in Combo box
To populate the Text box and List box I've requeried the boxes on the Combo
box AfterUpdate.

The Combo box works fine. The other two command boxes populate on the
screen but I can't use the values in them.

Using the following code I get 'Invalid use of null' error when it hits the
second line of code.
***code***
DIM categoryparm string
categoryparm = me.reflist

DoCmd.OpenForm stDocName, , , , , , categoryparm
***end code***

When I try to directly pass me.reflist in the openargs (DoCmd.OpenForm
stDocName, , , , , ,me.reflist) I get 'An expression you entered is the wrong
data type for one of the arguments'.

If this makes any sense at all and you are able to help then great! If not,
just tell me to go back to the drawing board.

Many thanks,
Nikkie


:

Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW



:

I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
C

Cheese_whiz

Now I'm officially confused :)

Do you have a text box (along with a combo box and a list box), and just
misidentified which control is the text box, or do you have the combo box and
two listboxes (and no text box)?

CW

Nikkiewolf said:
Sorry, my mistake. It is a list box.

N

Cheese_whiz said:
Hi again,

Is reflist not a text box?

CW

Nikkiewolf said:
Thanks again CW.

I have tried what you suggested but I get a compile error - 'Method or data
member not found' and the .text is highlighted in the VB debug.

Thanks,
Nikkie


:

Hi Nikkie,

All I was saying is that if a value......or, rather, if what you SEE in a
text box is NOT something that has been committed, and it can't be since your
text box is NOT bound, then I think you need to use the .text property to get
what you SEE in the text box.

So, instead of:

categoryparm = me.reflist

I think you might need to use:

categoryparm = me.reflist.text

I may be wrong but it's about a two second operation to test my theory and
it may help :)

Good luck!
CW

:

Hi CW,

Thanks for your response.

I am pretty new to Access so apologies for my set up explanation. I'm
probably over complicating things.

With some more playing around, I'm sure the problem is on my first form and
is something to do with the way I am using requery.

My table is 3 columns: Category, Category Label, Category Options. I use
the Category as lookup to populate combo boxes in other forms and tables with
a list of options.

To enable the users to review and add to the Category options I've created a
form with unbound command boxes that query the table as follows:
Combo box (me.combo9) - a select distinct on 'Category Label'
Text box (me.reflist) - Select distinct on 'Category' Where 'Category Label'
= value selected in Combo box
List box (me.list7) - Select on 'Category Options' where 'Category Label' =
value selected in Combo box
To populate the Text box and List box I've requeried the boxes on the Combo
box AfterUpdate.

The Combo box works fine. The other two command boxes populate on the
screen but I can't use the values in them.

Using the following code I get 'Invalid use of null' error when it hits the
second line of code.
***code***
DIM categoryparm string
categoryparm = me.reflist

DoCmd.OpenForm stDocName, , , , , , categoryparm
***end code***

When I try to directly pass me.reflist in the openargs (DoCmd.OpenForm
stDocName, , , , , ,me.reflist) I get 'An expression you entered is the wrong
data type for one of the arguments'.

If this makes any sense at all and you are able to help then great! If not,
just tell me to go back to the drawing board.

Many thanks,
Nikkie


:

Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW



:

I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
N

Nikkiewolf

This is my first post so things can only get better :)

I have the second, a combo box (combo9) that does a select distinct query
against the table and two list boxes (reflist & list7) that query the table
based on the value of the combo box. reflist return 1 record using a select
distinct and the other returns a list of options.

The boxes populate on screen but I can't seem to lift the value and pass it
to the new form, the message says the control is null.

Hopefully this makes things clearer? Thanks for being patient.

Nikkie



Cheese_whiz said:
Now I'm officially confused :)

Do you have a text box (along with a combo box and a list box), and just
misidentified which control is the text box, or do you have the combo box and
two listboxes (and no text box)?

CW

Nikkiewolf said:
Sorry, my mistake. It is a list box.

N

Cheese_whiz said:
Hi again,

Is reflist not a text box?

CW

:

Thanks again CW.

I have tried what you suggested but I get a compile error - 'Method or data
member not found' and the .text is highlighted in the VB debug.

Thanks,
Nikkie


:

Hi Nikkie,

All I was saying is that if a value......or, rather, if what you SEE in a
text box is NOT something that has been committed, and it can't be since your
text box is NOT bound, then I think you need to use the .text property to get
what you SEE in the text box.

So, instead of:

categoryparm = me.reflist

I think you might need to use:

categoryparm = me.reflist.text

I may be wrong but it's about a two second operation to test my theory and
it may help :)

Good luck!
CW

:

Hi CW,

Thanks for your response.

I am pretty new to Access so apologies for my set up explanation. I'm
probably over complicating things.

With some more playing around, I'm sure the problem is on my first form and
is something to do with the way I am using requery.

My table is 3 columns: Category, Category Label, Category Options. I use
the Category as lookup to populate combo boxes in other forms and tables with
a list of options.

To enable the users to review and add to the Category options I've created a
form with unbound command boxes that query the table as follows:
Combo box (me.combo9) - a select distinct on 'Category Label'
Text box (me.reflist) - Select distinct on 'Category' Where 'Category Label'
= value selected in Combo box
List box (me.list7) - Select on 'Category Options' where 'Category Label' =
value selected in Combo box
To populate the Text box and List box I've requeried the boxes on the Combo
box AfterUpdate.

The Combo box works fine. The other two command boxes populate on the
screen but I can't use the values in them.

Using the following code I get 'Invalid use of null' error when it hits the
second line of code.
***code***
DIM categoryparm string
categoryparm = me.reflist

DoCmd.OpenForm stDocName, , , , , , categoryparm
***end code***

When I try to directly pass me.reflist in the openargs (DoCmd.OpenForm
stDocName, , , , , ,me.reflist) I get 'An expression you entered is the wrong
data type for one of the arguments'.

If this makes any sense at all and you are able to help then great! If not,
just tell me to go back to the drawing board.

Many thanks,
Nikkie


:

Hi Nikkie,

Can you see the text box in question (the one on the first form that is
suppose to be the source of the value you want to pass to the second form)?
Does it actually have something in it? If it does, you might try using the
text property of that text box:

Instead of:

categoryParm = Me.reflist

Try:

categoryParm = Me.reflist.text

Hope that helps, but I admit I had a little trouble following your setup...
CW



:

I have a reference table which stores reference categories and options. I
have built a form that enables the user to view the available options by
category and add new options if necessary.

The user selects from a list of Category labels via a combobox (using a
Select Distinct against the table) and I populate a text box with the
category keyword (select distinct keyword where label = combobox) and a
listbox with category options (select options where label = combobox). I am
using REQUERY so that the textbox and listbox re-populate eachtime the user
chooses a new Category label via the combobox.

If the user wants to add a new category box they click on a button which
opens a new form. I want to pass across the Category keyword and Category
label into the new record on the new form but the value of the textbox is
alway null. I suspect this is due to the REQUERY but cannot fix it. Any
suggestions please? My code is as follows:

***FIRST FORM ***
Private Sub Combo9_AfterUpdate()

' Refresh content of Listbox each time a selection is made.

Me.List7.Requery
Me.reflist.Requery

End Sub

' Button that opens 'Reference update form' at a new record, passing across
' the category and Label selected.

Private Sub add_new_category_option_button_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim categoryparm As String
Dim labelparm As String

stDocName = "Reference update form"
categoryparm = Me.reflist
labelparm = Me.Combo9

'Open the Reference update form and pass reference
'field category and label into the new record
DoCmd.OpenForm stDocName, , , , , , labelparm & "$" & categoryparm

End Sub

***SECOND FORM***

Private Sub Form_Load()

Me.[Reference Field] = Me.OpenArgs
If Me.NewRecord Then
Me.[Reference Field] = Split(Me.OpenArgs, "$")(0)
Me.[Reference Field Label] = Split(Me.OpenArgs, "$")(1)
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenArgs problem 8
How to Pass value selected in Option Group to Form in OpenArgs? 1
How to Pass Option Group info to OpenArgs in OpenForm method? 1
erratic OpenArgs behavior 2
Printing from a form 2
Category and Subcategory 4
Outlook Userform Combobox Sort Code 0
Passing Values between forms 1

Top