Autofill value in form

[Bust_Ed]

Hello.

I'm totaly newbie in access coding. I've created my first DB, two tables and a form.

Tables are:
tbl_product

product|amount

pr1|100
pr2|200
pr3|250
etc...

tbl_subproducts

subproduct|amount|add|product
sub1|100|no| |
sub2|250|yes|pr3|

frm_subproducts is used for filling values. Main idea, that we fill only name of subproduct and amount of it (row 1), but in some cases it can be added to product and it's amount will be actually the same, as amount of product from tbl_product (row 2).

On my form, "subproduct" and "amount" are textboxes, "add" is checkbox and "product" is combobox (dropdown list). By default, "product" is disabled. I need to know, how can i:
1. When "add" checked - disable "amount" and enable "product".
2. When some value choosed in "product", for example pr2, amount of 200 (we'll take it from tbl_products) is filled in "amount".

I've googled and found this for step 2, but i've no idea, where should i use this code...

'************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
    varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
    varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
    If (Not IsNull(varState)) Then Me![State] = varState
    If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

Thank's in advance.

 

[James Dudden]

Try this:

if me!add = true then
me!amount.enabled = false
me!product.enabled = true
else
me!amount.enabled = true
me!product.enabled = false
end if

You will need to run this on the AfterUpdate of the "add" checkbox and the OnCurrent of the form.

 

[Bust_Ed]

You will need to run this on the AfterUpdate of the "add" checkbox and the OnCurrent of the form.

Thank you very much, worked like a charm.

 

[Bust_Ed]

Now i've stuck on next step...

I've wrote this sub:

Private Sub product_AfterUpdate()
strSQL = "SELECT tbl_product.amount FROM tbl_product where tbl_product.product = """ _
& Me!product & """"
Me!amount = strSQL
End Sub

When i'm changing "product" it says, that "The value you entered isn't valid for this field".

How can see, that exactly my query returns?

 

[James Dudden]

I don't think you don't need to do this in a sub. Try this:

1. In the query behind the combo-box (row-source) make sure amount is one of the fields and take a note of it's position - the first column is 0 so if it is the third column it's position is 2.

2. In the Control Source of the amount text box put this:
=Product.Column(2)

 

[Bust_Ed]

I've found solution

Dim varAmount As Variant
varAmount = DLookup("amount", "products", "product = product")
Me!Amount = varAmount

Works perfectly.

Thanks for your help anyway.

 

[Bust_Ed]

I have another problem.
I've switch my form to "Continuous Forms" view.

Now it shows multiple records. But when i'm marking checkbox on one record all other checkboxes get's marked automatically...

[solved - forgot to choose control source]

 

[Bust_Ed]

I have another problem.
I've switch my form to "Continuous Forms" view.

Now it shows multiple records. But when i'm marking checkbox on one record all other checkboxes get's marked automatically...

Fixed checkboxes, but no luck with script. Checking ONE checkbox, and ALL "product" become enable and ALL "amount" become disabled... Why does this happen?

 

[James Dudden]

That sounds like the checkbox isn't a field in the database. You need to add a field to your table called "Add" and make the Data Type Yes/No. Then re-open your form delete the old checkbox and add the new one from the field list.

 

[Bust_Ed]

Thanks. Fixed that, but what to do with that changing one-to-all... ^(

 

[James Dudden]

Fixed checkboxes, but no luck with script. Checking ONE checkbox, and ALL "product" become enable and ALL "amount" become disabled... Why does this happen?

I think this is bit of an Access bug. The other fields look like they're effected but if you click on them they are actaully enabeld. To get round this iyou could add this to your code:

if me!add = true then
me!amount.enabled = false
me!amount.locked = true
me!product.enabled = true
me!product.locked = false
else
me!amount.enabled = true
me!amount.locked = false
me!product.enabled = false
me!product.locked = true
end if

This does mean that you won't get a nice visual disabled view but at least all the others won't change and you still can't edit the field.

 

[Bust_Ed]

That won't work. I have five records. They're all on one form. If i check one checkbox, script will change all "product" and "amount" for ALL records. Is there a way to use script only for current record, not to whole form?

 

[James Dudden]

That should work - have you tested it?

 

[Bust_Ed]

Yes, it didn't work. When i'm marking one checkbox, script changes property for fields on ALL records, not on current record only.

That works perfectly, if i have one record on form, but my form is continuos...

 

[James Dudden]

I don't have a fix for that i'm afraid but like i said in my previous, post you can work around it but it won't be visual.

I think this is bit of an Access bug. The other fields look like they're effected but if you click on them they are actaully enabeld. To get round this iyou could add this to your code:

if me!add = true then
me!amount.enabled = false
me!amount.locked = true
me!product.enabled = true
me!product.locked = false
else
me!amount.enabled = true
me!amount.locked = false
me!product.enabled = false
me!product.locked = true
end if