vba form validierung apply same entry for all records

[Susy]

Hi there!



Is there a way to validate a field with either yes or no at a time?

What I mean, I have this form with, lets say 100 records.. but one field is most of the time empty and need to be filled with either Yes or No... It is only yes or no at a time.

The idea is to populate all records with either "yes" or "no" by just filling the first entry with either "yes" or "no". I attached a sample db so you can see what I mean.

Do I need to create some database connection and some eof property?

Ideas will be great.

Thanks

[ranman256]

you can add a yes/no field to the record. (boolean)
the default will be NO.
then you can set them to yes as needed.

[Susy]

No, that is not what I meant... it need to set all of them at once to either yes or no... to either Done or Not Done!

[davegri]

Attachment 30345
Here you go...

[Susy]

Hi Davegri,

I just finished with my code...

However, your code is only few lines long... amazing. Thanks

Option Compare Database
Option Explicit




Private Sub is_customer_DblClick(Cancel As Integer)


Dim mydB As DAO.Database
Dim myrs As DAO.Recordset
Dim rc As Long
Dim myfieldvalue As String


DoCmd.SetWarnings False






Set mydB = CurrentDb
Set myrs = mydB.OpenRecordset("tbl_import")


myfieldvalue = InputBox("enter the value you want to use")






        rc = myrs.RecordCount 
             
        MsgBox "You are going to modify " & rc & " record(s) at once!"
        
        
        myrs.MoveFirst
        
        
        
        Do While Not myrs.EOF
        
            myrs.Edit
            myrs.Fields("is_customer") = myfieldvalue
            myrs.Update
        
        
            myrs.MoveNext
    
        Loop
        
        Me.Refresh


    
DoCmd.SetWarnings True


myrs.Close
Set myrs = Nothing
Set mydB = Nothing


DoCmd.Save




End Sub

[davegri]

Yes, update queries can be very simple, compared with iterating thru a recordset... And MUCH faster for large tables.

[Susy]

Yes... I just learned something new again.. I called this knowledge transfer :-)

Private Sub is_customer_DblClick(Cancel As Integer)


Dim sSQL As String
    Dim myString As String
    
    myString = InputBox("Enter String")
    
    sSQL = "UPDATE tbl_Import SET is_Customer = '" & myString & "';"
    CurrentDb.Execute sSQL, dbFailOnError
    Me.Refresh
    DoCmd.Save




End Sub

Now I am asking myself how to only update a certain amount of field in a table...

1. let's say you are on the first record and just want to modify the first 20 records... and then
2. in record number 21 you want to update the values with another value or string...
--> 1 -20 with yes and then while being on record 21
-----> 21 to 100 with the value "no"...

Up the record where you click (initial position)... "question asking how many records do you want to modify: 20 for example" and so on..

Ideas?

[davegri]

davegri-testdb_yesno-Ver101.zip
OK, added another update query, and textboxes to enter the From-To record IDs.

[Susy]

Davegri,

very nice solved... I should have thought of that as well.

Thanks again!