What's wrong with my code? 1

[ychousa]

I have a frmDetail form which has, among many fields, txtSYear, txtSID, txtSQty, and txtSQty_Last.

The tblDetail has SID, SYear, and SQty, among many fields.

I want retrieve the SQty of last year from tblDetail to show in frmDetail.

For example, if txtSYear on the frmDetail is 2003 and SID is S-606, I want to retrieve SQty of 2002 from tblDetail where SID is S-606 and show it in txtSQty_Last on frmDetail.

I wrote the code in Afterupdate event of txtSYear as following, but it doesn't work.

Private Sub txtSYear_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT tblDetail.SQty FROM tblDetail WHERE tblDetail.SYear =" & CInt(Me.txtSYear) - 1 & "AND tblDetail.SID=" & Me!txtSID
Me!txtSQty_Last = strSQL
End Sub

Because datatype of SYear is text, I need to use CINT to convert it to a number.

What is wrong with my code?

thanks in advance.

John

 

[ychousa]

I think I could narrow down the problem area.

First, the record of txtSQty_Last doesn't follow the change of txtSYear when retrieving the record. I have a command button to go to next record, but SQty_Last year doesn't update when txtSYear changes by pressing button. Is Afterupdate event a right one to embed the code in in this case?


Second, the above code parses the whole statement into txtSQty_Last when user actually modify txtSYear. I need to parse the value of the SQL statement, not the whole statement.

Any suggestions to solve these two problems?

John

 

[Eupher]

I'll help with the 2nd problem:

Your code creates a string variable called strSQL and simply puts that string into your txtSQty_Last field on your form. What you want is to run the SQL string so that it opens a recordset, then you can assign a value from the recordset to txtSQty_Last. Like so:

Dim strSQL As String
Dim CurDB As Database
Dim Rs As Recordset

strSQL = "SELECT tblDetail.SQty FROM tblDetail " _
& "WHERE tblDetail.SYear =" & CInt(Me.txtSYear) - 1 & " _
& "AND tblDetail.SID=" & Me!txtSID

Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset(strSQL, dbOpenDynaset)
Me!txtSQty_Last = Rs!SQty
Rs.Close
Set Rs = Nothing
Set CurDB = Nothing

HTH...

Ken S.

 

[ychousa]

Thanks for your reply, Ken.

I got the idea and tried your code. but I got an error as follwing:

run time error 3601
Too few parameters. expected 1.

I think this error is caused by conversion of text(SYear) into number, resulting in data mismatch or something.

I think I made a mistake by making SYear field as a text. I should have made it as a number. Dang..

Because I have 10 or more relationships with SYear, changing SYear field in each table is very painful. I tried it with one relation, but I got an error that says no unique index is found or something.

How should I fix the code to make it right?

thanks alot.

 

[Eupher]

Hmm... Try this instead (in the On Open event):

Dim SQLStmt As String
Dim CurDB As Database
Dim Rs As Recordset
Dim strLYear As String

Me!SQty_Last = vbNullString

If Not IsNull(Me!SYear) Then
strLYear = Me!SYear - 1
SQLStmt = "SELECT SQty from tblDetail " _
& "WHERE SID = '" & Me![SID] & "' AND " _
& "SYear = '" & strLYear & "'"
Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
If Not Rs.EOF Then
Me!SQty_Last = Rs!SQty
Else
Me!SQty_Last = vbNullString
End If
End If

You're right that switching between data types on the fly like that can cause some problems. Note in my example above, though, that you can perform arithmetic functions on numeric string data. For instance, "2000" - 1 will return "1999", but "Joe" - 1 will cause an error. Of course, you can convert the data types if you want, using the int() and str() functions. But if you only do the conversion one way (string to integer) you'll get a "type mismatch" error when Access tries to evaluate your SQL statement (because SYear is a string, and intLYear is an integer). Also remember that the int() function adds a space to the beginning of the value to account for a possible negative sign, so you'll have to get rid of that when you convert back to a string or your SQL criteria will never match a record.

Not sure how you're using your form, but you'll probably need to create a similar event procedure in the After Update event of your [txtSYear] field to account for when your adding new records.

HTH...

Ken S.

 

[Eupher]

Oops - I meant you should put this code in an event procedure in the On Current event, not On Open.

Ken S.

 

[Eupher]

Ack! Typos in my post. I have some of your field names messed up in my previous example. Here's the correct code (I hope!). Sorry for any confusion...

Dim SQLStmt As String
Dim CurDB As Database
Dim Rs As Recordset
Dim strLYear As String

Me!SQty_Last = vbNullString

If Not IsNull(Me!txtSYear) Then
    strLYear = Me!txtSYear - 1
    SQLStmt = "SELECT SQty from tblDetail " _
    & "WHERE SID = '" & Me!txtSID & "' AND " _
    & "SYear = '" & strLYear & "'"
    Set CurDB = CurrentDb()
    Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
    If Not Rs.EOF Then
        Me!SQty_Last = Rs!SQty
        Else
            Me!SQty_Last = vbNullString
    End If
End If

Ken S.

 

[ychousa]

Hi, Ken. Thanks for your help.

I'll try the code on Monday and let you know what's happening.

Have a nice weekend!

John

 

[ychousa]

Hi, Ken.

I finally decided to go for changing all the SYear field in my tables and did it this morning.

Then, I tried the code in on current event of the form, but got a type-mismatch error.

Now, all my SYear field is Long Integer. What should I change in the code above?

thanks.

John

 

[Eupher]

I've changed the code to accomodate the new data type. Changes are in red. Since the lngLYear variable is now a numeric data type instead of a string, the variable is no longer surrounded by single quotes.

Dim SQLStmt As String
Dim CurDB As Database
Dim Rs As Recordset
Dim lngLYear As Long

Me!SQty_Last = vbNullString

If Not IsNull(Me!txtSYear) Then
lngLYear = Me!txtSYear - 1
SQLStmt = "SELECT SQty from tblDetail " _
& "WHERE SID = '" & Me!txtSID & "' AND " _
& "SYear = " & lngLYear
Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
If Not Rs.EOF Then
Me!SQty_Last = Rs!SQty
Else
Me!SQty_Last = vbNullString
End If
End If

HTH...

Ken S.

 

[ychousa]

Hi, Ken.

Hmm.. I still get a data mismatch error.

I doublechecked tblDetail.SYear is Long integer.

On VBA code, the line with "Set Rs = CurDB..." is highlited.

It seems that SQLStmt causes that error.

The SID field is text and looks like "S-606". Is the dash character gonna be the cause of the problem?

Otherwise, I really can't think of a reason..

I feel like I'm bugging you.. Sorry

 

[Eupher]

No apology necessary, that's what Tek-Tips is for. Okay, let's do a little debugging. First, comment out everything in the procedure beginning with the "Set Rs = CurDB" line onward, but leaving in the last "End If" line. Then insert:

Debug.Print SQLStmt

... immediately above the "Set Rs = CurDB" line. Re-compile, then close the code window, then close and re-open your form (we want the code to execute). Then open the code window for the form, open the Immediate window (if it's not already open), and post the SQL string that's displayed there. Then double check the data type of both the SID field and the SYear field. Let me know what you come up with.

Ken S.

 

[ychousa]

Hi, Ken.. Thanks.

Here's the SQL I got.

SELECT SQty from tblDetail WHERE SID = 'S-606' AND SYear = 2002

The txtSYear on the frmDetail was 2003, so the calculation was right.

How does it look?

 

[Eupher]

Looks okay to me. So let's find out which part of the SQL is messing up. Okay, now comment out the entire WHERE clause of the SQL, i.e. the two lines that begin with the ampersand, then UN-comment the "Set Rs = CurDB" line. You will need to remove the underscore character at the end of the SELECT line. You can also comment out the Debug.Print line. Set a break point at:

Me!SQty_Last = vbNullString

Then re-compile, close windows, close form, re-open form to execute code. Code execution will stop at the break point, so press F8 to step through the lines of code. Do you get an error message now? If it executed the entire procedure with no error, go back and UN-comment the first WHERE clause; you'll need to comment out the end of the line immediately before AND, put a closing double quote immediately after the single quote, and remove the underscore at the end of the line; so your code should now look like this:

Dim SQLStmt As String
Dim CurDB As Database
Dim Rs As Recordset
Dim lngLYear As Long

Me!SQty_Last = vbNullString

If Not IsNull(Me!txtSYear) Then
lngLYear = Me!txtSYear - 1
SQLStmt = "SELECT SQty from tblDetail " _
& "WHERE SID = '" & Me!txtSID & "'"
' & "SYear = " & lngLYear
' Debug.Print SQLStmt
Set CurDB = CurrentDb()
Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
' If Not Rs.EOF Then
' Me!SQty_Last = Rs!SQty
' Else
' Me!SQty_Last = vbNullString
' End If
End If

The re-compile, close window, close form, re-open to execute. Do you get the same error message? If so, we have a data type mismatch between SID in your table and txtSID in your form. If not, looks like a type mismatch between SYear and lngLYear. Give it a go and see what happens...

Ken S.

 

[ychousa]

Hi, Ken.

I got the same error..

It seems that I have a data type mismatch between SID in tblDetail and txtSID in frmDetail, and also SYear and txtSYear as you pointed out.(I tried it with SYear too)

I made the form using wizard. The data type for SID has not been changed at all even though that for SYear has been changed(from text to Long).

I also tried to make 2 unbound text boxes and set the control sources to each of the fields in tblDetail. And modified the code to accomodate new text box names. The same error..

I've been thinking that I don't need to touch a text box if it was created by form wizard-the wizard will do any neccessary jobs..

Am I wrong?

John

 

[Eupher]

Okay, now I'm stumped. If you'd like, you can e-mail me a copy of your database. Zip it if it's more than 3 megs or so. Maybe I can see what's going wrong if I lay my own eyeballs on it. kens_tt at comcast dot net

Ken S.

 

[Eupher]

Or as an alternative (or maybe in addition), I could send you my little test database and you could compare them. Whichever you prefer, let me know...

Ken S.

 

[ychousa]

Hi, Ken.

I just emailed to you at: kens_tt@comcast.net

How can I thank you enough for your help..

Have a great day!

John

 

[Eupher]

Okay, I got your database and took a look. The problem was in your references. We are using DAO objects, but ADO is the default for (I believe) Access 2K and later. You just need to open any code window, go to Tools->References, select the "Microsoft DAO 3.6 Object Library", then click the Priority up arrow until it's higher in the list than the ActiveX Data Objects reference. Re-compile and save everything, then I think you'll find everything works just fine.

Oh, when I compiled, the compiler barked about a reference to Me.Text95, so I changed it to Me!Text95, then no problems.

Ken S.

p.s. Looks like a fairly ambitious project. Good luck!

 

[ychousa]

Ken.. You're an angel!

Yes, it works beautiful.

I just need to do the rest of the last year's records(currencies) in on current event, I think.

Thanks A LOT!

John