Saving unbound text box data to a table 2

[A10Instructor]

Hi,

Been away from access for a while and need some help with unbound text boxes.

I have a form in which the header has several combo boxes that allow the user to select certain data (for example "courses"). Once they have made a selection, it then populates a corresponding unbound text box in the detail section of the form. This is working great and here is the code.

Private Sub Comboselcrs_AfterUpdate()
On Error Resume Next

    Comboselcrs.SetFocus
    
    Me.txtcrscode = Comboselcrs.Column(0)
    Me.txtcrsname = Comboselcrs.Column(1)
    Me.txtpdscode = Comboselcrs.Column(2)
    Me.txtcrshours = Comboselcrs.Column(3)
    Me.txtccafhours = Comboselcrs.Column(4)

Now I wish to take the data contained in the unbound text boxes save it to a record in a table called class_data when the user selects a button labeled 'Continue'.

I set this form up in the details section to give the user a chance to review the data selected, ensuring that it is correct. If not, they can make the change.

Eventually this data will be used to fill in a report that has a certificate as the background.

Any help is appreciated.

Thanks

A10 Instructor
"The World is My Classroom

 

[TheAceMan1]

How are ya A10Instructor . . .

An [blue]Append[/blue] query in the button should do nicely. The following code assumes the [blue]primarykey[/blue] of class_data is [blue]autonumber[/blue] and that the [blue]field names[/blue] are the same minus the prefix of [blue]txt[/blue]. If either is not true then modifications are necessary:

[blue]   Dim SQL As String
   
   SQL = "INSERT INTO class_data (crscode, " & _
                                 "crsname, " & _
                                 "pdscode, " & _
                                 "crshours, " & _
                                 "ccafhours) " & _
         "Values (Val(Forms!FormName!txtcrscode), " & _
                     "Forms!FormName!txtcrsname, " & _
                 "Val(Forms!FormName!txtpdscode), " & _
                 "Val(Forms!FormName!txtcrshours), " & _
                 "Val(Forms!FormName!txtccafhour));"
   DoCmd.RunSQL SQL[/blue]

See Ya! . . . . . .

 

[A10Instructor]

TheAceMan1,

Thanks for the quick reply and the code. I really appreciate it. I was really struggling with this one and the code works exactly as I wanted it to.

Thanks for the help




A10 Instructor
"The World is My Classroom

 

[A10Instructor]

TheAceMan1,

I waslooking at the table that the code you furnished me inputs to. Three out of the five fields work great. The crscode and pdscode fields do not populate as the other fields do.

I did some playing around and noticed that if the data for those fields begins with a number, it puts that first number into the field. For example, text box labeled txtpdscode on class_data_entry form contains the data 5AE. When I look at the table class_data, the pdscode field only contains the number 5. The letters 'AE' are nowhere to be seen. I'm at a loss to figure this one out. Psdcode field's data type is set to text. Any clues?

A10 Instructor
"The World is My Classroom

 

[TheAceMan1]

Roger That . . .

[blue]   Dim SQL As String
   
   SQL = "INSERT INTO class_data (crscode, " & _
                                 "crsname, " & _
                                 "pdscode, " & _
                                 "crshours, " & _
                                 "ccafhours) " & _
         "Values ([purple][b]Forms!FormName!txtcrscode[/b][/purple], " & _
                 "Forms!FormName!txtcrsname, " & _
                 "[purple][b]Forms!FormName!txtpdscode[/b][/purple], " & _
                 "Val(Forms!FormName!txtcrshours), " & _
                 "Val(Forms!FormName!txtccafhour));"
   DoCmd.RunSQL SQL[/blue]

See Ya! . . . . . .

 

[A10Instructor]

TheAceMan1,

Thanks for the help and quick reply. Out of curiosity, why did the removal of ' (Val ( ' make everything work when that same expression is located on last two lines?

Thanks again for the help! You are a great wealth of information.


A10 Instructor
"The World is My Classroom

 

[TheAceMan1]

A10Instructor . . .

The [blue]Val(string)[/blue] function converts alphanumeric to numeric (Although there are functions to convert to specific data types . . . I had no Idea what you were using) . . . enter the [blue]Val(string)[/blue] function. Its also the reason you got [blue]5[/blue] out of aplhanumeric [blue]5AE[/blue]. You should look it up in VBE help to get the full skinny on it.

My origional post of the code was my best guess of what was a string, what was numeric. Since it would've taken one more post to ask the data types anyway, I decided to give it my best guess (hoping to get it right) [blue]knowing you'd be back if you received a type error![/blue]

Tek-Tip: one of the greatest aids in access is [blue]Context Sensitive Help![/blue] . . . . In any VBE Editor window, put the cursor on a word and hit F1. This takes you directly to the word in help! . . . (if it exists). As a test, where you have the code above, put the cursor on [blue]Val[/blue] and hit F1 . . . I'll bet you never forget it! . . .

See Ya! . . . . . .

 

[A10Instructor]

TheAceman1,

Thanks for the info and the great tip. I'll try to keep that in mind for future issues. I apologize for not being as thorough in my original post about what data types I was using. I'll try to be more thorough next time!

Thanks

A10 Instructor
"The World is My Classroom

 

[TheAceMan1]

No sweat A10Instructor . . .

The magic word here is Resolution! . . . [blue]Beautiful isn't it![/blue]

See Ya! . . . . . .