varible column to update in an access database using dao

I would like to do next:
In a vb6 form I have 1 textbox, 2 listboxex, and one button
In list6 I have the columns of the table , I will use it to select the name of the column of the access database I want to change
In list66 I have the type of column of list6 values.
In textbox2 I will enter the value for the column I have choosen

I am using dao

When I click in the button I would like the program check if the textbox2.text is correct for the field selected.
If it is not correct, I will show a message "Sorry this value is not correct..."
If it is correct, I will update the record

my code is next:


    query$ = "select * from clients"
    Set ds5 = db.OpenRecordset(query$, dbOpenDynaset)
    If ds5.RecordCount <> 0 Then
    ds5.Movefirst
   

   
    If Not IsDate(textbox2) And List66.List(list6.listindex) = "dbDate" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbByte" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbInteger" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbByte" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbLong" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbCurrency" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbSingle" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbDouble" Then valor = "Error"
    If Not IsNumeric(textbox2) And List66.List(list6.listindex) = "dbLongBinary" Then valor = "Error"
   
    if valor ="Error" then
       msgbox "Error"
 else
     ds5.edit

    ds5!......  =   textbox2   *here is the problem, because the name of the field to update is in list6
    ds5.update

ds5.close


   
   
   
    Any help? Thank you very much.
LVL 10
aplimediaAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
Yes, I was thinking something like that as well.
Something like this Pseudo/untested code:

Public Function IsDataValid(YourValue As Variant, YourField As String) As Boolean
   
If Not IsDate(YourValue) And YourField = "dbDate" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbByte" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbInteger" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbByte" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbLong" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbCurrency" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbSingle" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbDouble" Then
    IsDataValid = False
ElseIf Not IsNumeric(YourValue) And YourField = "dbLongBinary" Then
    IsDataValid = False
End If

End Function

Open in new window


...Then do something like this on the form...
If IsDataValid(txtYourValue,lstYourFeild)=False Then
    Msgbox "Data is invalid"
    'Do more if needed
else
    'Insert the value into the designated record
    'Currentdb.execute "INSERT INTO YourTable (" &  lstYourField & ") Values (" & txtYourValue & ") WHERE YourID=" & me.txtYourID,dbFailOnerror
    
    'Or Update the Value...
    'Currentdb.execute "UPDATE YourTable SET " &  lstYourField & "=" & txtYourValue & " WHERE YourID=" & me.txtYourID,dbFailOnerror

Open in new window


JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Can you state what happens (or does not happen) when you run your code?
0
 
Jeffrey CoachmanMIS LiasonCommented:
Is that your entire code?
Not sure if, or where you are declaring your variables...
query$
ds5
valor
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
I may be misunderstanding something here...
What's the need for the recordset?
From your question, it seems like you are validating one value...?
0
 
Gustav BrockCIOCommented:
It would be much easier to just try and perform the update and catch the error which will be "Data type mismatch".
If so, pop the messagebox with a human readable error message.

/gustav
0
 
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for Jeffrey Coachman's comment #a40592361
Assisted answer: 250 points for Gustav Brock's comment #a40592295

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.