for loop on form(Datasheetview) , need help

[Trevor2007]

I have a subform with a loop to check to make sure 1 of the values in the textbox [CallType] = "N/A" before saving, but I can't seem to finish it, , well get it just to check for 1 N/A value, here is what I have :

Dim rst As DAO.Recordset
 
Set rst = Me.CallType
 
Do Until .EOF
rst ("CallType") <> "N/A"
    .MoveNext
    Set rst = Nothing
Loop
End With
 
If Me.[CallType] <> "N/A" Then Msgbox "you must have an N/A value"
 
End Sub

this is onclose event of form(witch is Datasheetview)
FYI: I do have a DOA referrrence

[Stewart Ross]

Hi Trevor. There are a number of problems in your code extract, which is not going to run just yet. A skeleton that opens a table or query and checks for the value you are trying to match is listed below.

Dim rst as DAO.RecordSet
Dim ValueFound as Boolean
ValueFound = False
set rst = CurrentDB.OpenRecordset("name of your query or table")
Do While Not rst.EOF
    ValueFound = rst![CallType] = "N/A"
    rst.Movenext
Loop
rst.Close
If ValueFound then
    {do stuff}
else
    {do something else}
endif

I guess when you say a textbox you mean a combo or a listbox. It is the recordsource for this you would need to open in the code above.

Personally, I'd use Access queries for this - which you can access using functions such as DLookup in code if you have to. The problem with taking a programmed approach is that all of the logic is hidden away from the users and maintainers of your application. Anyway, that's just my approach to it.

-Stewart

[Trevor2007]

Hi Trevor. There are a number of problems in your code extract, which is not going to run just yet. A skeleton that opens a table or query and checks for the value you are trying to match is listed below.

Dim rst as DAO.RecordSet
Dim ValueFound as Boolean
ValueFound = False
set rst = CurrentDB.OpenRecordset("name of your query or table")
Do While Not rst.EOF
    ValueFound = rst![CallType] = "N/A"
    rst.Movenext
Loop
rst.Close
If ValueFound then
    {do stuff}
else
    {do something else}
endif

I guess when you say a textbox you mean a combo or a listbox. It is the recordsource for this you would need to open in the code above.

Personally, I'd use Access queries for this - which you can access using functions such as DLookup in code if you have to. The problem with taking a programmed approach is that all of the logic is hidden away from the users and maintainers of your application. Anyway, that's just my approach to it.

-Stewart

Stewart, I'm thinking I went about this the wrong way , so here is what I am trying to do:
I have a form in Datasheet view(to have more controle over users. The datasheet is actualy a lookup table on a form, so that I can make check values before the lookup table itself is updated the field displayed on the form (in datasheetview) is [CallType] the form name is [form1], and I need to check to make sure 1 of the values is N/A , N/A count = 0 then mesgbox 1 of the values must be N/A, if N/A count = > 1 Then msgbox only 1 N/A value is required, please make the necessisary changes.