SQL question and a procedure question

[Thunder2781]

Hi all,

I have two questions:

1. I have a public procedure that i call in another procedure but in the second procedure I use a Exit sub but then it goes back to the first procedure but I would like to get out of both procedures. So that I return to my form. I hope that i'm clear.

2. I have a date field in my table but when I use a Insert query in VBA I get an error on that field because of a type mismatch, if the field is empty. What can i do about this? I prefer not to give a default value because it's a date field.

I hope somebody can help me out. If I'm not clear enough please ask.

 

[JPMontreal]

Hi,
1) Make your second procedure a function that return false instead of exit sub. In your first procedure, look for the result of the function and if it’s false exit the sub.
2) Give a null value for that field in your insert query.




Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[randy700]

You'll need to include code in the first procedure that allows you to exit early. I would assume you mean to do so depending on the outcome of the second procedure. Something like....

Public Function FirstProcedure()
    [i]any code required before calling the second procedure[/i]
    If SecondProcedure = False Then
        Exit Sub
    End If
    [i]additional steps if SecondProcedure returns 'True'[/i]
End Function

Public Function SecondProcedure As Boolean()
    [i]any necessary code[/i]
    If .... Then
        SecondProcedure = [b]False[/b]
        Exit Sub
    Else
        SecondProcedure = [b]True[/b]
    End If
    [i]more code if needed[/i]
End Function

Typed but not tested.........



Randy

 

[Thunder2781]

Thanks for your quick help .

I have done it a bit different. I have used a check boolean to see if it was true or false and then i used it like you discribed above.

But I still have the problem with the date field.
I have tried to make it Null but I still get an error that Access could not insert the field because of a convertion conflict.
I think that's because i'm using a text field in my form. So how can i convert the input of the text field into a date?

Thanks alot so far

 

[PHV]

IIf(IsDate(Me!yourTextBox), "#" & Format(Me!yourTextBox, "yyyy-mm-dd") & "#", Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Thunder2781]

Hi PH,

It's not working I get a compilation error that after the
Null) Access needs a "=".

Could you explain to me what this line does?

Thanks so far.

 

[PHV]

Could you please post your actual VBA code doing the insert ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Thunder2781]

I'm using the following code to insert a record into my table. It's dutch code so I hope you understand

And it's about the field dat_bouwdatum. On my form the field is not linked to the table because then the insert won't work properly. I tried the following but it won't work:

In my SQL qeury: dat_bouwdatum(date, #dd-mm-yyyy#)

DoCmd.RunSQL "INSERT INTO tblOnderdeel_gegevens " & _
"([klant], [project], [soort_bedrijf], [naam], [onderdeel], txt_Leverancier, num_Aantal, txt_Merk, " & _
"txt_Type_nummer, Num_Vermogen, txt_Serienummer, dat_Bouwdatum, num_Toerental, memo_Extra_gegevens)" & _
"VALUES ('" & keuzelijst_txtklant & "', '" & keuzelijst_txtproject & "', " & _
"'" & Keuzelijst_txtSoort_bedr & "', '" & keuzelijst_txtnaam & "', '" & keuzelijst_txtonderdeel & "', " & _
"'" & keuzelijst_txtLeverancier & "', '" & num_Aantal & "', '" & txt_Merk & "', '" & txt_Type_nummer & "', " & _
"'" & Num_Vermogen & "', '" & txt_Serienummer & "', '" & dat_Bouwdatum & "', '" & num_Toerental & "', " & _
"'" & memo_Extra_gegevens & "');"

Thanks so far.

 

[randy700]

"', #'" & dat_Bouwdatum & "'#, '"

or

"', '" & IIf(IsDate(dat_Bouwdatum), "#" & Format(dat_Bouwdatum, "yyyy-mm-dd") & "#", Null)

Randy

 

[Thunder2781]

Hi Randy thanks for your help.

But on both lines I get an error.

On the first line I get a Syntax error in date, in query expresion #"# but when i change the order of the characters I still get a syntax error.

On the second line I only get a syntax error with no discription why.

 

[Thunder2781]

Thanks for all youre help people

I have solved the problem. The date fields and the numeric fields didn't need " or ' or & characters and that's what I did wrong.

Thanks again