Inserting data onto a form via combo box

[techkenny1]

Hi all,
I have a client form on which resides a combobox (for convienience) which has on the Row source:
"SELECT QBroker.Companyname, QBroker.BrokerStaffID FROM QBroker"

What I want to do is to be able to select the company from the combobox and insert it into another form which has a text box "company"
I have tried using this code but I can not get it to work;

Private Sub cboInvoice_AfterUpdate()
On Error Resume Next

Dim rs As ADODB.Recordset
Dim strSQL As String

cboInvoice.SetFocus
If cboInvoice.Value > 0 Then
strSQL = "SELECT * FROM QBroker WHERE Brokerstaffid = " & cboInvoice.Value

Set rs = CreateObject("ADODB.Recordset")
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open strSQL, CurrentProject.Connection

If rs.State = 1 Then
If Not rs.BOF Then

Me.BrokerStaffID = rs("Brokerstaffid")
Me.Companyname = rs("Companyname")


End If
rs.Close
End If
Set rs = Nothing
End If
Docmd.openForm "Invoice"
End Sub

Any help wpuld be appreciated to get this to work

 

[dhookom]

insert it into another form

Forms don't store values.
This line of code isn't necessary

cboInvoice.SetFocus

Are you expecting to see a particular record when you open the form?

Generally, if you want to add a record to a table, I would create an append SQL statement and then execute it.

I'm not sure what exactly you are attempting to accomplish with your code.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookham,
Thanks for your reply.
WhatI want to do is to is this. In a table I have company names and addresess etc.
I just want to select a company via a combo box and for that information to be inserted into a another form.(formA)

For example I could open the company form and use a command button which would insert into the form 9(FormA) the company name etc.
'on the commnd button on the company form, I could use:
gstrCompanyname = Me.Companyname and
when formA opens on the; onload event me.companyname = gstrcompanyname. This would then insert the data onto formA.
However I wondered if this same event could be done by using a combo box, selecting the company and for the same info to be inserted in FormA.

I hope this makes some sense!

 

[dhookom]

I'm still not sure why you would want to do this when you could just place a combo box on FormA. I suppose you could hide the form with the combo box and then reference it with code from FormA. You could also open FormA with an OpenArgs to send the value to FormA.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Perhaps...

gstrCompanyname = dLookup("CompanyName", "QBroker", "BrokerStaffID = " & [i]ComboBoxName[/i])

Randy