Accessing a table with a missing object

[kewelch]

Hello, this is my first post and I am ready to learn Acess 2010!!!. The error I am running into says that I am missing an object within the conditional statement of this if structure. Any help would be much appreciated.

    If tblAdjustment.AdjustmentTypeID = "6" Or "7" Then        total = total + tblAdjustment.AdjustmentQuantity
    Else
        total = total - tblAdjustment.AdjustmentQuantity
    End If

-Kevin The Intern

[ssanfu]

Welcome to the forum..

OK, there are several things going on here.

First, there are two forms of the IF function. One form is the single line syntax. It was used to be used in the early days to save on program space.
It is the single line form :

If A = B THEN C=c+12

The other form is the multiline:

If A = B Then 
   C = C+12
End IF

The multiline syntax is the most used because it allows you more instructions within the function (and is easier to read).

Second, you cannot refer to a field in a table using the syntax you provided. You have to use a bound form or a recordset in code.

If Me.AdjustmentTypeID = 6 Or Me.AdjustmentTypeID = 7 Then        
    total = total + Me.AdjustmentQuantity
Else
    total = total - Me.AdjustmentQuantity
End If

If you use a bound form, you would use the name of the control that is bound to the field. The "Me" is a shortcut that refers to the form that is active.

Notice that the control name is repeated in the first line. You have to explicitly name the control for multiple comparisons.
And finally, a number 6 is different than a text 6 ("6"). Putting quotes around a number (known as delimiting) or letter(s) tells Access "This is a string of text".

[kewelch]

Hi Steve, thanks for the thorough reply. I have fixed the syntax issues associated with this logic structure, but I am still running into the issue of accessing different data. It is not a bound form, so it sounds like a table based recordset is what I need. Also, I will be looking to gather data from a couple different tables and looking to join the recordsets together as a single record. Is this possible? And where can I learn to use these types of recordsets? The msdn website is confusing to me at this point.

[ssanfu]

It is much easier to use bound forms; Access does all of the heavy lifting. If you use unbound forms, you have to write code to read the data from tables/recodsets and write the edits/new records to the table/recodsets.

A recordset can be a query, opened using VBA, or the SQL of the query, assigned to a variable, that is opened using VBA.

I don't have a list of books (although I should); search this forum on "Access Books" or "book recommendations". There are lots of postings.