appending record based on combo box choices

[train2]

Am I being incredibly thick?

I've created a form with various combo boxes- the form is not based on any table or query but "homemade". I would like to append a record to a table and thought that creating a button to run a macro to run the append query would work. But I think my understanding of append query must be wrong - do I have to specify a "from table" and a "to table"? If so, is it possbile to append from a form that is not based on a table?

Perhaps you can help - can i create simple code to append the record instead of the longwinded way of macro / query etc.

Would appreciate help - this shouldn't be so difficult.
Train2

 

[train2]

Should anyone look at this to find answers for themselves, I've solved this by adding code to run an SQL insert query using a string and a DoCmd.RunSQL statement.

I'm new to this and trying things out. I think using recordsets would be cleaner, but i've not used them before and the SQL is simple and easy to understand.

 

[cavery]

Can you elaborate on this please? I'm having similar issues, I have a form, where I want users to click an option box, append records and then delete those records, all within one step. I am aware that I will have to use macros, which is fine, but I'm just in the beginning stages....any help is appreciated.

Thanks,
Clark

 

[train2]

OK hope this helps. I've just copied the code for starters. I was also completely new to VB and only a user rather than a programmer and still managed to get it working so don't worry. It isn't perfect code but it works.

Basically I have a pupil moving from one class to another. So on the form I ask for input: FromClass, Pupil, ToClass. I then update the old class record to make it inactive, then add the new class record. However, if a new class record already exists, it just reactivates it. Below includes also the little messages and error checking that make it prettier!
Good luck- let me know how you get on.
Train2


Private Sub OK_Click()
Dim Msg, Response, NewSet, OldSet, UpdateProgress, clearbox As String
Dim Existrec As Integer
' just basic declarations for variables

' Checks if data is present and sensible in all fields and gives message accordingly
clearbox = ""
Msg = clearbox
Response = clearbox

If IsNull(Me.cboFromclass) Or IsNull(Me.cboPupil) Or IsNull(Me.cboToclass) Then
Msg = MsgBox("Sorry - not enough information entered. Please enter all fields.", vbOKOnly)
ElseIf (Me.cboFromclass) = (Me.cboToclass) Then
Msg = MsgBox("Please check the fields - at the moment the classes are the same.", vbOKOnly)

Else
' updates old class with deactive flag
OldSet = "Update T_ClassPupil " & _
"SET [Active] = FALSE " & _
"WHERE [C_ID]='" & [cboFromclass] & "' AND " & _
" [P_ID]='" & [cboPupil] & "'"
' checks to see if a records for newclass already exists
Existrec = Nz(DCount("[P_Id]", "[T_ClassPupil]", "[P_Id]= '" & cboPupil & "' AND [C_Id] = '" & cboToclass & "'&quot, 0)
' if not add anew record, else just update old one
If Existrec = 0 Then
NewSet = "Insert into T_ClassPupil (C_Id, P_Id) " & _
"VALUES ('" & [cboToclass] & "','" & [cboPupil] & "')"
ElseIf Existrec > 0 Then
NewSet = "Update T_ClassPupil " & _
"SET [Active] = TRUE " & _
"WHERE [C_ID]='" & [cboFromclass] & "' AND " & _
" [P_ID]='" & [cboPupil] & "'"
End If
' updates all progress records from old to new class
UpdateProgress = "Update T_Progress " & _
"SET [C_ID] = '" & [cboToclass] & "' " & _
"WHERE [C_ID]='" & [cboFromclass] & "' AND " & _
" [P_ID]='" & [cboPupil] & "'"

Msg = "You're about to move " & (Me.Firstname) & " " & (Me.Surname) & " from " & [cboFromclass] & " to " & [cboToclass] & "."
Response = MsgBox(Msg, vbOKCancel)
If Response = 1 Then
DoCmd.RunSQL OldSet
DoCmd.RunSQL NewSet
DoCmd.RunSQL UpdateProgress
' once done, clear up all fields and blank them.
Msg = (Me.Firstname) & " " & (Me.Surname) & " is now in " & [cboToclass] & "."
Response = MsgBox(Msg, vbOK)
[cboFromclass] = clearbox
[cboToclass] = clearbox
[cboPupil] = clearbox
[Firstname] = clearbox
[Surname] = clearbox
[Tutorgroup] = clearbox
Else
'Do nothing
End If
End If

End Sub

 

[train2]

I take it you know how to add the code to a form - if not, let me know.
Train2