?
Solved

varible column to update in an access database using dao

Posted on 2015-02-05
7
Medium Priority
?
68 Views
Last Modified: 2015-02-28
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.
0
Comment
Question by:aplimedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40591730
Can you state what happens (or does not happen) when you run your code?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40591732
Is that your entire code?
Not sure if, or where you are declaring your variables...
query$
ds5
valor
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40591755
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40592295
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 40592361
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40637440
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question