What is wrong with this code? strings in VB

[bella]

HI,

Can someone please tell me how to write the following in VB - using all the right syntax. I am using DAO and i cant make it work. I'm getting syntax errors

"Insert into

" & _
"VALUES (w,x, y,z)" &_

Where:

1. x is a string that the user types into a text box on a form
2. y is a string that the user selects from a combo box
3. z is a string that the user selects from a combo box

4. w is an integer that represents the last table index+1
(i.e. if the table has 51 records currently, the value for w after adding a new record to this table is 52)


PLEASE help...

Bella

 

[AncientOne]

We need the names of the fields where the values go and the names of the combo boxes and text boxes, please.

 

[WayneRyan]

Bella,

Dim dbs As Database
Dim rst As Recordset
Dim sql as String

Set dbs = Currentdb
sql = "Select * from YourTable"

Set rst = dbs.OpenRecordset(sql)

rst.AddNew
rst!w = DMax("[w]", "YourTable") + 1
rst!x = Me.x
rst!y = Me.y
rst!z = Me.z
rst.Update

Wayne

 

[bella]

reply to Ancient One

HI Ancient One,

->We need the names of the fields where the values go and the -->names of the combo boxes and text boxes, please.

1. The fields where the values go are called:

SL_ID, Subject Name, Year_ID and KLA_ID (in that order)


2. The Names of the combo boxes (all containing text values) are:

1. subject
2. kla
3. year

3. The SL_ID field is an integer which needs to increment by 1 for every new update.

4. The name of the table that I need to insert the values into is called:

Subject List


any help would b greatly appreciated.

Bella
-----------------------------------------------------------------------------------

"Insert into

" & _
"VALUES (w,x, y,z)" &_

Where:

1. x is a string that the user types into a text box on a form
2. y is a string that the user selects from a combo box
3. z is a string that the user selects from a combo box

4. w is an integer that represents the last table index+1
(i.e. if the table has 51 records currently, the value for w after adding a new record to this table is 52)


PLEASE help...

Bella

 

[bella]

reply to Wayne

Hi Wayne or anyone else out there...

thanks for your code, but i realised i did not explain my question properly -

I have a text box, and 2 combo boxes (call them x, y, z)
and they all store text values.

I need to put those text values into a table called Subject List
which has the following fields:

SL_ID (int) -> needs to increment by one for every new record
Subject Name (String) -> gets value from the text box x
Year_ID (int) -> gets value from combo box y
KLA_ID (int) -> gets value from combo box z


Year_ID and KLA_ID in the Subject List Table are ID's that correspond to the text values that the users enter into from the form.

So I need to say something like:
IF y (combo box value) = "blah" THEN
rst.y = 1
IF Y = "blah blah" THEN
rst.y = 2
etc

and also
IF z (combo box value) = "blah" then
rst.z = 1
etc

and then finally update the Subject Table with->
- SL_ID as an int
- Subject Name as a String
- KLA_ID as an INT
- Year_ID as an INT

can u make the code u wrote me, do that? I tried to do the IF else, but its not working. I dont really understand DAO recordsets in VB - particularly the syntax thereof...

any help would b greatly appreciated.

Bella

 

[WayneRyan]

bella,

Maybe something along these lines.

Dim dbs As Database
Dim rst As Recordset
Dim sql as String

Set dbs = Currentdb
sql = "Select * from YourTable"

Set rst = dbs.OpenRecordset(sql)

rst.AddNew
' Get next number
rst!w = DMax("[w]", "YourTable") + 1
If Me.SomeCombo = "Blah" Then
   rst!x = 1
ElseIf Me.SomeCombo = "BlahBlah" Then
   rst!x = 2
Else
   rst!x = 3
End If
rst!y = Me.y
rst!z = Me.z
rst.Update

Why don't you convert your code, run it and
post any problems here. Backup your db First!

Wayne

 

[bella]

Reply to Wayne

Hi, i cut and pasted this code her but it gives me an error:

"Run time Error 13
Type Mismatch"

and then the debugger has the yellow arrow pointing at:
>Set rst = dbs.OpenRecordset(sql)


?? Bella
-------------------------------------------------------------------------------
Dim dbs As Database
Dim rst As Recordset
Dim sql As String


Set dbs = CurrentDb
sql = "SELECT [Subject List].[SL_ID], [Subject List].[Subject Name], " & _
"[Subject List].[Year_ID], [Subject List].[KLA_ID]" & _
"FROM [Subject List];"

Set rst = dbs.OpenRecordset(sql)


rst.AddNew

' Get next number
rst!w = DMax(SL_ID, "[Subject List]") + 1
rst!subject = Me.subject

If Me.kla = "Creative Arts" Then
rst!kla = 1
ElseIf Me.kla = "English" Then
rst!kla = 2
ElseIf Me.kla = "Human Society & its Environment" Then
rst!kla = 3
ElseIf Me.kla = "Languages Other than English" Then
rst!kla = 4
ElseIf Me.kla = "Mathematics" Then
rst!kla = 5
ElseIf Me.kla = "Personal Development, Health & Physical Education" Then
rst!kla = 6
ElseIf Me.kla = "Religious Education" Then
rst!kla = 7
ElseIf Me.kla = "Science" Then
rst!kla = 8
ElseIf Me.kla = "Technological & Applied Studies" Then
rst!kla = 9
End If


If Me.Year = "Year 7" Then
rst!Year = 1
ElseIf Me.Year = "Year 8" Then
rst!Year = 2
ElseIf Me.Year = "Year 9" Then
rst!Year = 3
ElseIf Me.Year = "Year 10" Then
rst!Year = 4
ElseIf Me.Year = "Stage 5" Then
rst!Year = 5
End If

rst.Update



End Sub

 

[WayneRyan]

bella,

You will have to explicitly declare DAO.

You will also have to select the DAO reference and promote
it above ADO. In code view, Tools --> References

Dim dbs As DAO.Database 
Dim rst As DAO.Recordset 
Dim sql As String 


Set dbs = CurrentDb 
sql = "SELECT [Subject List].[SL_ID], [Subject List].[Subject Name], " & _ 
             "[Subject List].[Year_ID], [Subject List].[KLA_ID]" & _ 
      "FROM [Subject List];" 

Set rst = dbs.OpenRecordset(sql) 

rst.AddNew 

' Get next number 
rst!w = DMax(SL_ID, "[Subject List]") + 1 
rst!subject = Me.subject 

If Me.kla = "Creative Arts" Then 
   rst!kla = 1 
ElseIf Me.kla = "English" Then 
   rst!kla = 2 
ElseIf Me.kla = "Human Society & its Environment" Then 
   rst!kla = 3 
ElseIf Me.kla = "Languages Other than English" Then 
   rst!kla = 4 
ElseIf Me.kla = "Mathematics" Then 
   rst!kla = 5 
ElseIf Me.kla = "Personal Development, Health & Physical Education" Then 
   rst!kla = 6 
ElseIf Me.kla = "Religious Education" Then 
   rst!kla = 7 
ElseIf Me.kla = "Science" Then 
   rst!kla = 8 
ElseIf Me.kla = "Technological & Applied Studies" Then 
   rst!kla = 9 
End If 


If Me.Year = "Year 7" Then 
   rst!Year = 1 
ElseIf Me.Year = "Year 8" Then 
   rst!Year = 2 
ElseIf Me.Year = "Year 9" Then 
   rst!Year = 3 
ElseIf Me.Year = "Year 10" Then 
   rst!Year = 4 
ElseIf Me.Year = "Stage 5" Then 
   rst!Year = 5 
End If 

rst.Update 

End Sub

Wayne

 

[bella]

Wayne

Hi Wayne,

I did that, and its now giving me the following error:

Run time Error 2428:
"You entered an invalid argument in the domain aggregate function"

and the denugger points to :

> rst!w = DMax(SL_ID, "[Subject List]") + 1

w is not refereceing something that the user enters or selects. its just an Integer that stores the incremented value of SL_ID. Do i have to declare it somewhere :

eg. Dim w As Integer

before I can use it? I tried that and it still doesnt work...

the users only input three values - subject name, kla, and year.

SL_ID is a Subject_ID field in the table, and it just needs to increment each time the user adds a new record.


??

Bella

 

[WayneRyan]

bella,

' Get next number
rst!SL_ID = DMax("SL_ID", "[Subject List]") + 1

Need quotes ...

And I think that it is rst!SL_ID

Wayne

 

[bella]

hi sorry if this is a super dumb question, but in the following line of code:


If Me.kla = "Creative Arts" Then
rst!KLA_ID = 1


does:

Me.kla refer to the combo box called kla that the user selects on the form?

and rst!KLA_ID refer to the field in the Subject List table which i am trying to update to?

is that how this syntax works?

Bell

 

[WayneRyan]

bella,

You got it ...

Wayne

 

[AncientOne]

wow! a lot has happened since I went to bed.

May I make some suggestions?

First, as you are getting your kla values from a combo box, it would be easier to make the combo return the kla_ID directly. That way, you won't have to have all those If statements in your code. (To help the operator, you can still display the "friendly" long name when a selection is made).You just need to modify your combo so it has two columns instead of one.

Second, if you don't want to make a change at this stage, it would be preferable to use a Select Case or Switch method to align KLAs to their respective IDs.

Third, it is still possible to use your original SELECT INTO...VALUES syntax and substitute your combo and text box names if you want. Possibly at this stage, you don't!

I'll butt out now. Good luck!

 

[bella]

Hi there

thanks for your help its greatly appreciated. I stil have some questions/problems:

1. When i run the code it compiles but right down the bottom where we say:

rst.Update it tells me that it cant update Year_ID values, because these do not exist in the linked Year Groups table.

But, this isnt true...because I have explicitly declared Year_ID's 1 to 5 in the Year Groups table. so I don't understand why its giving me this error...

any ideas??

---

2. Ancient One, if I add a second combo box column that stores KLA_IDs and KLA_Names (and Year Ids & Year text values) is it possible to only display the text column for the user?

would it put u out greatly to write me the INSERT INTO code that would work for my problem - as currently my buttons still aint working because of the weird thing mentioned in #1. so any code that actually does the update is good code...

greatfully,

Bella

 

[AncientOne]

bella

I am going to make the assumption that the row source for your combo is based on a list which you have entered yourself. If not, correct me.

If you add a column to your combo, make it the first column. define it as a number of the type corresponding to kla_id, (probably a long ) and make each row correspond to the kla_ID and description, ie

Row 1

col1 1 col2 "Creative Arts"

Row 2

col1 2 col2 "English"

Go to the properties list for the combo when you have finished. Make sure the column count is 2
Make the bound column=1
Make the Column width for column 1=0
Make the column width for column2=the width of the longest field in col2,which you might have to experiment with to find.

When you select the combo, you will see the description, but the value of the combo will actually be the number in the first (hidden) column.

Since this value corresponds to the kla_ID, you can reference it directly in your code;

rst!kla=me.kla

and so completely bypass all the if statements.

 

[AncientOne]

On your other problem;

What is the Year Group table referred to in the error message?

Your recordset is based on a table called subjectlist

Is this table joined by a relationship to another table called Year group which enforces referential integrity? And are you attempting to enter a value in the subject list table that doesn't exist in the Year group table.

If so, altering the approach to a straight SQL statement won't help!

NB: the combo box fix can also be applied to Year, by the way.

 

[bella]

Woo hoo!!

Hi guys,

THANKS! - it finally worked

My year groups problem - i still dont get it - but i fixed it, typing in the Year Group Values into the combo box, instead of getting those from a table as I had done b4.

thanks heaps

!!

Bell