Updating table through parameters from Combo Box

[alphil2001]

Hi All
Hope you can help with this one I have been going round in circles trying to figure it out.

I have a form called Add Student which has a number of text boxes to allow the information to be entered.

First Name
Last Name
Mobile
Telephone
Office (Combo box list of offices)

Address (Populated from office combo)
Code from unbound field in the control source is =[forms]![Add Student]![office].Column(4)

Address2 (Populated from office combo)
Area (Populated from office combo)
City (Populated from office Combo)
PostCode (Populated from office combo)

I have a combo box that has a list of offices that the user can choose from in the drop down list. The list has the office name, Address, Address2, Area, City and post code listed.
Once the user has selected the office from the combo list, other unbound fields are populated, so when a user selects the office "Accrington" from the combo box the Address fields etc are populated.

The probem I am having is that once this information has been filled in, I want to use a Append query to fill in two seperate tables. The append query will populate the tables with the field values if the user has physically typed into the fields (such as mobile and first and last name) the problem I am having is that the text that is populated in the unbound fields from the combo box is not being transfered but the physically typed text from First name and last name is being transfered so I know the query and the code is ok. Is there a different setting or code I need to use to pass or change value of the text from the unbound boxes that have been filled in automatically to the query as this isnt happening.

Here is my code:
Private Sub UpdateStudent_Click()
End Sub
On Error GoTo errhand

Dim frmstr As QueryDef
Dim dbservice As Database
Dim dbservice2 As Database

Set dbservice = CurrentDb
Set frmstr = dbservice.QueryDefs("UpdateStudent")
frmstr.Parameters![StudentID] = Me.StudentID
frmstr.Parameters![OfficeName] = Me.OfficeName.Text
frmstr.Parameters![RMU] = Me.RMU.Text
frmstr.Parameters![FirstName] = Me.FirstName.Text
frmstr.Parameters![LastName] = Me.LastName.Text
frmstr.Parameters![Directline] = Me.Directline.Text
frmstr.Parameters![Mobile] = Me.Mobile.Text
frmstr.Parameters![Phone] = Me.Phone.Text
frmstr.Parameters![Directorate] = Me.Directorate.Text
frmstr.Parameters![Jobtitle] = Me.Jobtitle.Text
frmstr.Parameters![Address] = Me.Address.Text
frmstr.Parameters![Address2] = Me.Address2.Text
frmstr.Parameters![Area] = Me.Area.Text
frmstr.Parameters![City] = Me.City.Text
frmstr.Parameters![PostalCode] = Me.PostalCode.Text
frmstr.Parameters! = Me.EMail.Text
frmstr.Parameters![UserName] = Me.UserName.Text
frmstr.Parameters![Notes] = Me.Notes.Text
frmstr.Parameters![Comments] = Me.Comments.Text
frmstr.Execute
Set dbservice2 = CurrentDb
Set frmstr = dbservice2.QueryDefs("UpdateStaff")
frmstr.Parameters![StudentID] = Me.StudentID
frmstr.Parameters![OfficeName] = Me.OfficeName.Text
frmstr.Parameters![FirstName] = Me.FirstName.Text
frmstr.Parameters![LastName] = Me.LastName.Text
frmstr.Parameters![Mobile] = Me.Mobile.Text
frmstr.Parameters![Phone] = Me.Phone.Text
frmstr.Parameters![Directorate] = Me.Directorate.Text
frmstr.Parameters![Jobtitle] = Me.Jobtitle.Text
frmstr.Parameters![EMail] = Me.EMail.Text
frmstr.Execute

Exit_rtn:
Exit Sub

errhand:
DoCmd.Hourglass False
Err.Clear
'Resume Exit_rtn

Exit_UpdateService:
Exit Sub

Do I also have to have the code for the second table in a seperate Sub form or can they be left as they are

Thank you for your help
Alphil2001

 

[AlexCuse]

I haven't been able to use .Text in the way you are trying to, it always tells me that I need to have the focus. Try using .Value instead.

I also normally use the RunSQL method in situations like this, not sure if that is better or not, but it might be worth looking into.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[IGPCS]

me too in access vbe you cant use the .text after the object name only if you do a setfocus to that object and i do agree think RunSql is better and faster

 

[alphil2001]

Thanks AlexCuse and IGPCS Ill have alook into this and try it
Thanks for all your help
Alphil2001

 

[AvesRule33]

Alphil21 - did you get this to work? I am working on a similar procedure (Trying to update a TABLE with results from a FORM with fields autopopulated via a combo box). I tried using your code (Changing to my criteria) but can not get it to append the record to my table and then clear the form so the user can select another Unit # to update the work order.

Any help or input would be Greatly Appreciated!

Thanks,

AvesRule33

 

[IGPCS]

you can use an update query if the tables have field that are unique

 

[AvesRule33]

I do not want to update the records, only append them to the table as a new record and then clear the form and make it ready for another entry. I tried creating an append query making each fields criteria =[forms]![myformname]![myformfield] but it did not work?

 

[IGPCS]

well you can use an append query as well

 

[IGPCS]

something like this

 INSERT INTO tableinstertinginto ( field1, field2, field3, field4 )
select * from tablewithinfo ( field1, field2, field3, field4 )
where recordid

hope you got it

 

[AvesRule33]

Here is the code I am using to try and accomplish this. Maybe you can tell me what I'm missing (Probably something obvious!)

Private Sub cmdAddRecord_Click()

Dim strSQL As String
Dim dbservice As Database
Dim db As Database

Set dbservice = CurrentDb
strSQL = "Insert Into [Repairs] "
strSQL = [UnitNo] = Me.[UnitNo].Value
strSQL = [EquipType] = Me.[EquipType].Value
strSQL = [Type] = Me.[Type].Value
strSQL = [Make] = Me.[Make].Value
strSQL = [ModelNo] = Me.[ModelNo].Value
strSQL = [SerialNo] = Me.[SerialNo].Value
strSQL = [Location] = Me.[Location].Value
strSQL = [ServiceDate] = Me.[ServiceDate].Value
strSQL = [WorkOrderNo] = Me.[WorkOrderNo].Value
strSQL = [Hours] = Me.[Hours].Value
strSQL = [Description] = Me.[Description].Value
db.Execute strSQL

End Sub

Thank You Very Much for taking the time to work with me on this. Much Appreciated!

 

[alphil2001]

Hi AvesRule33

I managed to get it to work but a little differently for what I was wanting it to do.

At first I had unbound fileds with the =[combobox].column(1) to bring over the value which worked fine but it wouldnt pass the value over to the append query.

The solution I found was to change the fields back to bound fields but in the combo box (Office) After Update event procedure I put in the code:

Private Sub Office_AfterUpdate()
OfficeName = Me.Office.Column(1)
RMU = Me.Office.Column(3)
Address = Me.Office.Column(4)
Address2 = Me.Office.Column(5)
Area = Me.Office.Column(6)
City = Me.Office.Column(7)
PostalCode = Me.Office.Column(8)
Directline = Me.Office.Column(9)
End Sub

This passed the value to the bound fields (RMU etc)and then the append query worked fine

Hope this helps
Thanks IGPCS for all your help, it was your thoughts and help that lead me to find this solution

Great Site for help, Brilliant
Alphil2001

 

[AvesRule33]

Here's another way to get it accomplished - Thought I would pass this along. A Big THANK YOU goes out to AlexCuse for helping me with this one.

Private Sub cmdAddRecord_Click()

Dim strSQL As String
Dim db As Database

Set db = CurrentDb

DoCmd.SetWarnings (False)
strSQL = "INSERT INTO [Repairs](UnitNo, EquipType, Type, Make, ModelNo, SerialNo, Location, ServiceDate, WorkOrderNo, Hours, Description) Values ('" & UnitNo.Value & "','" & EquipType.Value & "', '" & [Type].Value & "', '" & Make.Value & "', '" & ModelNo.Value & "', '" & SerialNo.Value & "', '" & Location.Value & "', '" & ServiceDate.Value & "', '" & WorkOrderNo.Value & "', '" & Hours.Value & "', '" & Description.Value & "')"
DoCmd.RunSQL strSQL

End Sub

 

[TheAceMan1]

How are ya alphil2001 . . .

Whats wrong with [blue]using a bound form[/blue] to store initial data to the prime table and an [blue]Append[/blue] query to append to table2? . . . I don't even see the need for Query Defs here! . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .