can someone help me with an if statement in a modual

[R9772]

can someone help me create a function in Access that uses if statements to act on tables.

for instance (select *,if [my design] = [new product] then [new funding] / 12 into [new column] from products)

thanks

 

[docmeizie]

Looks like you are trying to mix SQL and VBA together in one line. Trying writing out what you want this code to do. That way we can help you more. Include table relations, how they are connected, and exactly what you want the above statement to do.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[WayneRyan]

R,

This is just a vague answer, because it is not clear
from your question what your table structure is and
what kind of form you have.

This assumes that you have controls on your form named
ProductName and NewFunding. They relate to two data
fields in YourTable.

You can put this code in the AfterUpdate event of
your form.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

Set dbs = CurrentDb

'
' Check to see if product is new, or old
'
sql = "Select * " & _
      "From   YourTable " & _
      "Where  ProductName = '" & Me.NewProduct & "'"
Set rst = dbs.OpenRecordset(sql)
If rst.EOF and rst.BOF Then
   ' New Product
   rst.AddNew
   rst.ProductName = Me.ProductName
   rst.NewFunding = Me.NewFunding
   rst.Update
Else
   ' Existing product
   rst.Edit
   rst.NewFunding = rst.NewFunding
   rst.Update
End If

Wayne

 

[R9772]

i apologize for being unclear. i hope the following is a little better description than i provided earlier.

my first table has the following columns cn(customer name), prod(old products) and funding. my second has designs(new products), co(company), funding(new funds allotted) and cst(cost).

what i want to do is replace old products funding amount with new funding wherever they match on "designs" and "prod". if possible,i would like to create a new column that tracks where i make changes with the amount of those changes.


thanks again for you help.