dcsimg
Results 1 to 19 of 19

Thread: [RESOLVED] Vb6 run-time error 3421

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    59

    Resolved [RESOLVED] Vb6 run-time error 3421

    I have 4 text boxes on a form they are used to enter dates.
    The dates are saved to MS acces.
    The table field is set to date.

    When I leave a text box empty it gives.

    run-time error 3421
    data-type conversion error

    How can I trap this error

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,584

    Re: Vb6 run-time error 3421

    You would need to show your code.

    The best way to handle the error depending on how your code is written would be to verify that the user entered a valid date before trying to update the database so no error occurs at all. Other methods are to set up an error handler either by using On Error Resume Next followed by a check of the error number or an on error goto errorhandler with a error handler block at the bottom of the sub.

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    59

    Re: Vb6 run-time error 3421

    I'm making a breeding card for birdbreeding.
    So if a make the card I only have the date of first Coupled
    or when there is an egg DateFirstEgg as you see I tried to put 0 in txtbox but that also
    doesn't work

    This is the code it goes wrong at
    rsBreedingCard!DateFirstEgg = .txtDateFirstEgg.Text
    that is the first error


    Code:
    Public Sub NewBreedingCard(TheForm As Form)
    
        With TheForm
              If .cmdPairLock.Visible = True Then                       
                intAnswer = MsgBox(strPair, vbOKOnly)
                Exit Sub
            End If
                
                If .txtCoupled = "" Then                                
                    intAnswer = MsgBox(strCoupled, vbOKOnly)
                    Exit Sub
                End If
                                   
                    If .txtDateFirstEgg.Text = "" Then
                      .txtDateFirstEgg.Text = 0
                    End If
                         If .txtTotallEggs.Text = "" Then
                            .txtTotallEggs.Text = 0
                         
                         End If
                         
                         If .txtHatch.Text = "" Then
                            .txtHatch.Text = 0
                         End If
                             
                             If .txtQtyHatched.Text = "" Then
                                .txtQtyHatched.Text = 0
                             End If
                                 
                                 If .txtRinged.Text = "" Then
                                    .txtRinged.Text = 0
                                 End If
                                 
                                     If .TxtFlyOut.Text = "" Then
                                        .TxtFlyOut.Text = 0
                                     End If
              
              rsBreedingCard.AddNew
                  rsBreedingCard!PairNumberID = .txtPairNumber.Text
                  rsBreedingCard!RoundID = .txtRound.Text
                  rsBreedingCard!Coupled = .txtCoupled.Text
                  rsBreedingCard!DateFirstEgg = .txtDateFirstEgg.Text
                  rsBreedingCard!TotalEggs = .txtTotallEggs.Text
                  rsBreedingCard!DateHatch = .txtHatch.Text
                  rsBreedingCard!QtyHatched = .txtQtyHatched.Text
                  rsBreedingCard!DateRinged = .txtRinged.Text
                  rsBreedingCard!DateFlyOut = .TxtFlyOut.Text
                  rsBreedingCard!PairNumberID = .txtPairNumber.Text
                  rsBreedingCard!RoundID = .txtRound.Text
                  rsBreedingCard!Coupled = .txtCoupled.Text
              rsBreedingCard.Update
       
        
        ButtonHideShow frmBreedingCard, "SaveCard"             
        
        FormClear frmBreedingCard, "Card"                      
        
        FormClear frmBreedingCard, "Pairs"                      
        
        
        End With
    End Sub

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,602

    Re: Vb6 run-time error 3421

    You are attempting to put a text object into a date field....won't quite work. Either change or field type or make the statement put in a date vice text.

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,584

    Re: Vb6 run-time error 3421

    In the case of a date field you have 3 options assuming your database layout does nto prevent it.
    1: Use a valid date
    2: Do not include this field as part of the update when there is not a valid date entered allowing the date to either remain unchanged or be null
    3: Update with a null if there is no valid date.

    Date fields can contain only valid dates or Nulls and nulls are only allowed if they are not restricted in your table design. If nulls are not allowed then you must enter a valid date in all cases.

    Of course you could change the field type to text and put anything in there you want but you will not be able to sort by date then unless you reformat the string to be YYYY/MM/DD Or YYYYMMDD

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    59

    Re: Vb6 run-time error 3421

    I tried with a nulls they are allowed by table but still gives the error.
    I also tried validation rule that also didn't work
    The on error resume next works but how do I check the error number
    or isn't that the way to do it

  7. #7
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,602

    Re: Vb6 run-time error 3421

    Did you change your .txtDateFirstEgg.Text (for example) to a DATE?

    I don't normally use direct assignments to fields like you do (however that is perfectly fine), but I usually use (as my preference) a cmd object and to insert a 'date' in 'string' format, I use something like this:

    cmd.commandtext = "Insert into myTable (myDate) values ("
    cmd.commandtext = cmd.commandtext + "#1/1/1900#)"
    set recordset = cmd.execute

    (of course I have dimensioned all this in advance.) SO, if you insert your .txtDateFirstEgg.Text in that manner, it may work....

    DataMiser can describe quickly, I am sure....I just don't use that type of 'insert' as a rule for my programs...

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,602

    Re: Vb6 run-time error 3421

    of you want to see what error is being thrown, do something like this:

    On Error GoTo errHandler

    'your code is here


    errHandler:
    MsgBox Err.number + " - " + err.description
    Exit Sub

    'end sub of your code

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    59

    Re: Vb6 run-time error 3421

    @ SamOscarBrown
    I tried to set the textbox to a date that didn't work
    I'm a beginner so I try everything and learn from it.
    I'm going to try with a string as you said

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,602

    Re: Vb6 run-time error 3421

    as DM stated, if you change your db field to a text type, and you want to sort later, you use the format he stated. You could also convert those strings to dates later and then sort by date if you so desired.
    I'll hunt up the proper way to directly assign a date to a field using your method of assignment and get back with you (unless DM beats me to it!! :-)

  11. #11
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,584

    Re: Vb6 run-time error 3421

    It is pretty simple really
    Code:
    if isdate(.txtRinged.Text) Then
        rsBreedingCard!DateRinged = CDate(.txtRinged.Text)
    End IF
    To handle an error properly when using On Error Resume Next
    Code:
    On Error Resume Next
    ' Line of code which may cause an error
    If Err.Number<>0 Then
          'Error occured
    End IF
    Note in a case such as this the .Update will trigger the error but the source would be one of the field assignments that came before it.

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,602

    Re: Vb6 run-time error 3421

    @DataMiser
    The simplistic things are the easiest to forget! I'm always trying to over analyze my thought process before coding, when the simple solutions are sitting right there in MSDN (and the furthest reaches in the back of my head). Thx.

  13. #13
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Vb6 run-time error 3421

    I think I'd leave the Table Definitions as Dates and just check and convert the values in the appropriate TextBoxes prior to adding / updating with a little Function
    Code:
    rsBreedingCard.AddNew
    rsBreedingCard!PairNumberID = .txtPairNumber.Text
    rsBreedingCard!RoundID = .txtRound.Text
    rsBreedingCard!Coupled = .txtCoupled.Text
    rsBreedingCard!DateFirstEgg = TestDate(.txtDateFirstEgg.Text)
    rsBreedingCard!TotalEggs = .txtTotallEggs.Text
    rsBreedingCard!DateHatch = TestDate(.txtHatch.Text)
    rsBreedingCard!QtyHatched = .txtQtyHatched.Text
    rsBreedingCard!DateRinged = TestDate(.txtRinged.Text)
    rsBreedingCard!DateFlyOut = TestDate(.TxtFlyOut.Text)
    rsBreedingCard!PairNumberID = .txtPairNumber.Text
    rsBreedingCard!RoundID = .txtRound.Text
    rsBreedingCard!Coupled = .txtCoupled.Text
    rsBreedingCard.Update
    
    Private Function TestDate(ByVal strData As String) As Date
    If strData = vbNullString Then strData = "0"
    If IsDate(strData) Then
        TestDate = CDate(strData)
    Else
        TestDate = CDate("0")
    End If
    End Function
    where there is a zero or Null value, the date will be set to '30/12/1899 00:00:00' (or '12/30/1899 00:00:00' depending on your locale)

    I guess that the birds involved are slightly younger than that, so when you
    come to read the dates back you can test for that Date and display zero or Null.

    Note that this assumes you have already validated non-zero entries in the TextBoxes containing Dates to make sure that they are valid dates.
    Last edited by Doogle; Oct 20th, 2012 at 02:26 AM.

  14. #14

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    59

    Re: Vb6 run-time error 3421

    Thanks guys for the help.
    @DataMiser
    I tried the
    if isdate(.txtRinged.Text) Then

    But that still gives the error

    @Doogle
    The function works it only put 12:00 AM in the field.
    Not date '30/12/1899 00:00:00' like you said

    I've set textbox dateformat to date
    Would the problem be also in my database?
    In table at design I just set the data type to date/time!
    for the rest Ileft everything as it is!

  15. #15
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Vb6 run-time error 3421

    I think you'll find that if you format the date it will be as I said
    Code:
    Debug.Print Format(.txtRinged.Text,"dd/mm/yy hh:mm:ss")
    assuming .txtRinged.Text is not Null

  16. #16

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    59

    Re: Vb6 run-time error 3421

    Do I need this line for every textbox that contains date
    and where do I put this line in my code

  17. #17
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Vb6 run-time error 3421

    No, you only need to worry about the value when you read the Dates. You'll need to check whether it represents a '0' value or whether it's a real date.

    The code in Post #13 just checks the dates in the TextBoxes and if they're 0 or null it puts in a default date.

    When you read the Dates back you just need to do a simple check. Assume you've read a row into a RecordSet named rs1, you could populate your TextBoxes something like this:
    Code:
    .txtPairNumber.Text = rs1![PairNumberID]
    .txtRound.Text = rs1![RoundID]
    .txtCoupled.Text = rs1![Coupled]
    .txtDateFirstEgg.Text = ConvertDate(rs1![DateFirstEgg])
    .txtTotallEggs.Text = rs1![TotalEggs]
    .txtHatch.Text = ConvertDate(ts1![DateHatch])
    .txtQtyHatched.Text = rs1![PtyHatched]
    .txtRinged.Text = ConvertDate(rs1![DateRinged])
    .TxtFlyOut.Text = ConvertDate(rs1![DateFlyOut])
    .txtPairNumber.Text = rs1![PairNumber]
    .txtRound.Text = rs1![RoundID]
    .txtCoupled.Text = rs1![Coupled]
    
    
    Private Function ConvertDate(daDate As Date) As String
    If CDbl(daDate) <> 0 Then
        ConvertDate = Format(daDate, "dd/mm/yyyy") ' You may want to use "mm/dd/yyyy"
    End If
    End Function

  18. #18
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,584

    Re: Vb6 run-time error 3421

    @DataMiser
    I tried the
    if isdate(.txtRinged.Text) Then
    Did you try it the way I showed but on all of the date fields?

    I hate it when someone says I tried that but does not show us how they did it because most of the time they just did not do it right.

    The way Doogle showed you to put it in a little function is better as it will work with less lines of code and will not be placing any null values in the database.

  19. #19

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    59

    Re: Vb6 run-time error 3421

    @DataMiser
    You are wright I had it in the wrong places.
    When I first tried it I had it outside the rsBreedingCard.addNew
    I tried it the way below and it works it also leaves the table field blank.

    I don't understand every thing that quick because I'm a beginner.
    The answers on this was all a bit confusing but I tried them all now.
    They all work now so my question what is the best way?
    I think your way because it leaves the fields blank and then I have no problem retrieving the data.
    Code:
    rsBreedingCard.AddNew
              
                  rsBreedingCard!PairNumberID = .txtPairNumber.Text
                  rsBreedingCard!RoundID = .txtRound.Text
                  rsBreedingCard!Coupled = .txtCoupled.Text
              
               If IsDate(.txtDateFirstEgg.Text) Then
                     rsBreedingCard!DateFirstEgg = CDate(.txtDateFirstEgg.Text)
               End If              
                  
                  rsBreedingCard!TotalEggs = .txtTotallEggs.Text
               
               If IsDate(.txtHatch.Text) Then
                     rsBreedingCard!DateHatched = CDate(.txtHatch.Text)
               End If              
                  
                  rsBreedingCard!QtyHatched = .txtQtyHatched.Text
                 
               If IsDate(.txtRinged.Text) Then
                     rsBreedingCard!DateRinged = CDate(.txtRinged.Text)
               End If              
                 
               If IsDate(.TxtFlyOut.Text) Then
                     rsBreedingCard!DateFlyOut = CDate(.TxtFlyOut.Text)
               End If           
                 
    rsBreedingCard.Update

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width