Controlsource

[djeeten]

Hi,

I'm having this little problem.

I'm creating this program that allows customers to place an order.

First he has to choose on a small form which kind of products (productgroup) he wishes to order. Once the customer has confirmed that, frmOrders is loaded, where the customer can only choose those products that belong to the choosen productgroup.

Now, a customer also has several budgets (one for each productgroup he is allowed to place an order for).

I put those budgets in a seperate table:

tblBudget
---------
BudgetId
Budget
CustomerId
ProductGroupId

My problem is that I can't find a way to load the budget into a textfield on frmOrders, once the form is loaded.

I tried this:

Me!Budget.ControlSource = "Select Budget FROM tblBudget WHERE CustomerId=" & Me!CustomerId & " AND ProductGroupId=" & Me!ProductGroupId

This gives a false value in the textfield (#name).

Could anyone help me please?

Thanks in advance,

dj.

 

[jebry]

Hi dj!

In the control source of the budget control put:

=DLookUp("Budget", "tblBudget", "CustomerID = " & Me!CustomerID & " And ProductGroupID = " & Me!ProductGroupID)

hth
Jeff Bridgham
bridgham@purdue.edu

 

[djeeten]

Hi,

thanks Jeff Bridgham, that did help.

But now I have this problem: as the customer chooses several products on the form, the budget changes as well.

This happens in a different textfield 'NewBudget'. Whenever a product is choosen (together with its amount) or changed, the value in the textfield 'NewBudget' changes as well.

When the customer confirmes the order by clicking on a button, I try to update the table tblBudget like this:

stSql = "UPDATE tblBudget SET Budget =" & Me!NewBudget & " WHERE CustomerId ='" & Me!CustomerId & "' AND ProductGroupId =" & Me!ProductGroupId

This gives a syntax error, but I have no idea why.

Also when I try to assign the value in Me!NewBudget to Me!Budget, I get an error (cannot assign value).

Any idea why?


Thanks,

dj.