write to database, one button, multiple tables

[dntel123]

Hey,

I've tried writing some code that will, when you click the add to database button, look for the value in a dropdown box, use that as the recordset, then let you enter the information in using input boxes.

I'm not sure if it will let me do it but i came up with this code, i dont think it likes me trying to make the value of the drop down a recordset, can anyone see whats wrong with this code?

Private Sub cmdAdd_Click()

Dim MYDB As Database
Dim SOURCE As Recordset


Set MYDB = CurrentDb()
Set SOURCE = MYDB.OpenRecordset("Me!cmbModules")


SOURCE.MoveFirst
Do Until SOURCE.EOF


SOURCE.AddNew
SOURCE![First Name] = InputBox("Please enter Students First Name")
SOURCE![Last Name] = InputBox("Please enter Students Last Name")
SOURCE![email] = InputBox("Please enter Students Email")
SOURCE![Grade] = InputBox("Please enter Students Grade")
SOURCE.Update

ONWARD:

SOURCE.MoveNext
Loop
End Sub

Thanks for looking
Liam

 

[Rabbie]

Can you tell us what you have in your combo box(drop down). It's a bit hard to put your code into context without knowing this.

Your code doesn't actually make much sense because it looks like you are trying to open a record set at the beginning and add a new record for each record that is actually already there. Is that whay you want or do you want to update each existing record?

Please give an explanation of what you are trying to accomplish and details of your data structure and we may be able to help you.

 

[dntel123]

Yer sorry

Its ok now i've fixed it:

Private Sub cmdAdd_Click()

Dim MYDB As Database
Dim SOURCE As Recordset
Dim table As String


table = Me!cmbModules
Set MYDB = CurrentDb()
Set SOURCE = MYDB.OpenRecordset(table)


SOURCE.MoveFirst



SOURCE.AddNew
SOURCE![First Name] = InputBox("Please enter Students First Name")
SOURCE![Last Name] = InputBox("Please enter Students Last Name")
SOURCE![email] = InputBox("Please enter Students Email")
SOURCE![Grade] = InputBox("Please enter Students Grade")
SOURCE.Update

ONWARD:

SOURCE.MoveNext

End Sub

Basically I have a a query that just lists the 4 modules:

Javascript
Database
Armpit
Networking

The drop down box is sourced to that so it displays these Values, i had to rename the tables to what was in the drop down then i could use:

Set SOURCE = MYDB.OpenRecordset(table)

So now which ever module is selected in the drop down is used as the record set, so when i click add button, brings up 4 list boxes and bam, all information in, bit of a weird way of doing it really but it worked at least

 

[dntel123]

Although this all works properly, when the list boxes pop up, if u click cancel for all of them then it crashes any idea how i could stop tht?