Rookie Dlookup vs Default Value....

  • Thread starter Thread starter Ray Gibson
  • Start date Start date
R

Ray Gibson

I have a subform that is very simple.

Sku, Descripton, Qty, Price.

I want the description field to automatically be filled in (and editable)
after keying a sku.

The dlookup function kind of works, but I need to be able to edit the field,
and I can't since its bound.

I set the Subform.Description Field control sorce to
=DLookUp("[Item].[Description]","[Item]","[ItemNum] =
Forms![subFrm_ContractItems]![Sku]")

(I don't like how I have to hit F9 to even get the Description to display!)

How can I set it up to dump the initial value of the Item.Description field
into the subform and then allow me to modify it if need be?

Thanks in advance ....
ray
 
Try using the dlookup function in the Default Value of
the field and then make the Description field in the
table the control source. Then you should be able to
edit the value.

HTH

Byron
 
I have a subform that is very simple.

Sku, Descripton, Qty, Price.

I want the description field to automatically be filled in (and editable)
after keying a sku.

The dlookup function kind of works, but I need to be able to edit the field,
and I can't since its bound.

I set the Subform.Description Field control sorce to
=DLookUp("[Item].[Description]","[Item]","[ItemNum] =
Forms![subFrm_ContractItems]![Sku]")

(I don't like how I have to hit F9 to even get the Description to display!)

How can I set it up to dump the initial value of the Item.Description field
into the subform and then allow me to modify it if need be?
Click to expand...

What's the Recordsource of the subform? Are you trying to copy a
description from one table into another table? When you edit it, do
you want to edit it in the (I presume that you have a) product table,
or do you want to allow for one description in the product table and a
DIFFERENT description in this table?
 
First off, thanks for the reply.

The record set of the subform is the "many" side of a 1 to many relationship
between Tbl_Contract and Tbl_ContractItems. There is only one more table,
ITEM : contains all product (sku) info.

I want to set the "initial" value of my DESCRIPTION field in
Tbl_ContractItems via the subform to the value of the DESECRIPTION field in
my product table (ITEM). So, yes, I want the ablity to have different value
in both tables. (Basically my user would make a more detailed description
than the stock one...)




John Vinson said:
I have a subform that is very simple.

Sku, Descripton, Qty, Price.

I want the description field to automatically be filled in (and editable)
after keying a sku.

The dlookup function kind of works, but I need to be able to edit the field,
and I can't since its bound.

I set the Subform.Description Field control sorce to
=DLookUp("[Item].[Description]","[Item]","[ItemNum] =
Forms![subFrm_ContractItems]![Sku]")

(I don't like how I have to hit F9 to even get the Description to display!)

How can I set it up to dump the initial value of the Item.Description field
into the subform and then allow me to modify it if need be?
Click to expand...

What's the Recordsource of the subform? Are you trying to copy a
description from one table into another table? When you edit it, do
you want to edit it in the (I presume that you have a) product table,
or do you want to allow for one description in the product table and a
DIFFERENT description in this table?
Click to expand...
 
Thanks for the reply....

I read somewhere that this means that the default (INITIAL) value would be
blank. ie. It's too late, once you see the field, the default value has
already been set.

I tried it anyway, and my description field is blank. The only time I can
get it to display the info is by putting the dlookup function in the control
source.


Byron said:
Try using the dlookup function in the Default Value of
the field and then make the Description field in the
table the control source. Then you should be able to
edit the value.

HTH

Byron
-----Original Message-----
I have a subform that is very simple.

Sku, Descripton, Qty, Price.

I want the description field to automatically be filled in (and editable)
after keying a sku.

The dlookup function kind of works, but I need to be able to edit the field,
and I can't since its bound.

I set the Subform.Description Field control sorce to
=DLookUp("[Item].[Description]","[Item]","[ItemNum] =
Forms![subFrm_ContractItems]![Sku]")

(I don't like how I have to hit F9 to even get the Description to display!)

How can I set it up to dump the initial value of the Item.Description field
into the subform and then allow me to modify it if need be?

Thanks in advance ....
ray


.
Click to expand...
Click to expand...
 
First off, thanks for the reply.

The record set of the subform is the "many" side of a 1 to many relationship
between Tbl_Contract and Tbl_ContractItems. There is only one more table,
ITEM : contains all product (sku) info.

I want to set the "initial" value of my DESCRIPTION field in
Tbl_ContractItems via the subform to the value of the DESECRIPTION field in
my product table (ITEM). So, yes, I want the ablity to have different value
in both tables. (Basically my user would make a more detailed description
than the stock one...)
Click to expand...

ok... just wanted to be sure what you wanted to do!

That said, you'll need just a little bit of VBA code. I'm assuming
that you have a Combo Box to enter the sku into tbl_ContractItems.

If so, include the Description in that combo box's RowSource query. In
the AfterUpdate event of the combo box put code like

Private Sub cboProduct_AfterUpdate()
Me!txtDescription = cboProduct.Column(2)
End Sub

where txtDestcription is the name of the textbox bound to the
DESCRIPTION field in tbl_ContractItems, cboProduct is the name of the
combo box, and (2) means the third field in the combo's rowsource
query (it's a zero based subscript) - adjust all as needed.
 
Thanks John, that worked.

I was not using a combo box to lookup the sku before, I changed that and
added the description to it. Perfect.....

Just on more side question.... The combo box looks up the sku from the ITEM
table. This table is huge (its an ODBC link) 300,000 records, so it takes
awhile to populate the combo. Any secrets for speeding this up???
 
Just on more side question.... The combo box looks up the sku from the ITEM
table. This table is huge (its an ODBC link) 300,000 records, so it takes
awhile to populate the combo. Any secrets for speeding this up???
Click to expand...

That can be a real problem - in fact it's worse than you think: combo
boxes are limited to 65536 rows!

Is there any way to limit the SKU's to a subset? Surely you don't have
all 300,000 items in stock?

There are some tricks to dynamically populate a combo box, I'd have to
do some digging to find them. Searching http://www.mvps.org/access or
see Allen Browne's suggestions at
http://members.iinet.net.au/~allenbrowne/ser-32.html
 
That is a problem. Is there another way to populate the description field
without using the combo box ?

(Funny, that's how this whole thing started!)

Thanks for all of your time John.
 
That is a problem. Is there another way to populate the description field
without using the combo box ?
Click to expand...

Yes; you can use VBA code in the SKU control's AfterUpdate event to
either open a Recordset based on the SKU table or use DLookUp. With
that big a table I'd do the former:

Private Sub txtSKU_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Description] FROM tblSKU" _
& " WHERE [SKU] = '" & Me!txtSKU & "'")
If rs.RecordCount > 0 Then
Me!txtDescription = rs!Description
Else
MsgBox "This SKU was not found", vbOKOnly
End If
End Sub
 
Thanks John,

You're the man!


John Vinson said:
That is a problem. Is there another way to populate the description field
without using the combo box ?
Click to expand...

Yes; you can use VBA code in the SKU control's AfterUpdate event to
either open a Recordset based on the SKU table or use DLookUp. With
that big a table I'd do the former:

Private Sub txtSKU_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Description] FROM tblSKU" _
& " WHERE [SKU] = '" & Me!txtSKU & "'")
If rs.RecordCount > 0 Then
Me!txtDescription = rs!Description
Else
MsgBox "This SKU was not found", vbOKOnly
End If
End Sub
Click to expand...
 
I'm using your code as suggested and get this error:
Compile Error: User-defined type not defined...

It highlights: db as DAO.Database

? This Dim Command is defining ?


John Vinson said:
That is a problem. Is there another way to populate the description field
without using the combo box ?
Click to expand...

Yes; you can use VBA code in the SKU control's AfterUpdate event to
either open a Recordset based on the SKU table or use DLookUp. With
that big a table I'd do the former:

Private Sub txtSKU_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Description] FROM tblSKU" _
& " WHERE [SKU] = '" & Me!txtSKU & "'")
If rs.RecordCount > 0 Then
Me!txtDescription = rs!Description
Else
MsgBox "This SKU was not found", vbOKOnly
End If
End Sub
Click to expand...
 
I figured it out. I added the necessary DAO under Tools - References.

Thanks. It all works well.


Ray Gibson said:
I'm using your code as suggested and get this error:
Compile Error: User-defined type not defined...

It highlights: db as DAO.Database

? This Dim Command is defining ?


John Vinson said:
That is a problem. Is there another way to populate the description field
without using the combo box ?
Click to expand...

Yes; you can use VBA code in the SKU control's AfterUpdate event to
either open a Recordset based on the SKU table or use DLookUp. With
that big a table I'd do the former:

Private Sub txtSKU_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Description] FROM tblSKU" _
& " WHERE [SKU] = '" & Me!txtSKU & "'")
If rs.RecordCount > 0 Then
Me!txtDescription = rs!Description
Else
MsgBox "This SKU was not found", vbOKOnly
End If
End Sub
Click to expand...
Click to expand...
 
Back
Top