put a record in a drop down box

[chembuff1982]

Here's my code, combo27 is working fine, however when I update my table Test (field Test) which works fine, I can't get Combo29 to update to the added test I just put in. I need to use the acLast or something like it to bring my record up, which works, however, it's numeric. I need to know how to bring the string value in the cell back to my drop down Combo29 here's my full code and form for the whole database, take a looksee if you want.

[IMG]C:\Documents and Settings\Justin \Desktop\form.j pg[/IMG]

Private Sub cmdprint_Click()
On Error GoTo Err_cmdprint_Click
Dim stDocName As String
    Dim MyForm As Form

    stDocName = "tbldate"
    Set MyForm = Screen.ActiveForm
    DoCmd.SelectObject acTable, stDocName, True
    DoCmd.PrintOut
    DoCmd.SelectObject acForm, MyForm.Name, False
    Text54.Value = Now()
    Text54.Visible = True
    
Exit_cmdprint_Click:
    Exit Sub

Err_cmdprint_Click:
    MsgBox Err.Description
    Resume Exit_cmdprint_Click
End Sub

####################

Private Sub Combo27_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
    rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo27], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    Me.Combo29.Requery
  
   Combo29.Visible = True
   
End Sub

######################

Private Sub Combo27_Change()
  cmdprint.Visible = False
  Dim strCustomer As String

' define string

        strCustomer = strCustomer & "" & Me.Combo27.SelText & ", "

'appending the value to string from box
  
    ' remove excess off string in append
    strCustomer = Left(strCustomer, Len(strCustomer) - 2)
   pass (strCustomer)
  
End Sub

#############
  
Private Sub Combo29_AfterUpdate()
 
 cmdprint.Visible = True
 
End Sub

#######################

Private Sub Combo29_Change()
Dim strTest As String ' define string Test to use for append

        strTest = strTest & "" & Me.Combo29.SelText & ", " 'append string from combobox

    ' to remove excess comma stored in append
    strTest = Left(strTest, Len(strTest) - 2)
    pass2 (strTest)
    
    cmdprint.Visible = True
    DoCmd.GoToRecord acDataForm, "Test", acLast

End Sub

########################

Private Sub Form_Load()
  Text54.Visible = False
  Combo29.Visible = False
  cmdprint.Visible = False

End Sub

#######################

Sub pass(x As String)
    
   Dim store As String
   
  store = x
  If cmdprint.Visible = True Then
  
   Dim strSQL As String

   strSQL = "INSERT INTO TablePrint (CustomerPrint) " & _
       "VALUES('" & store & "')"
   DoCmd.RunSQL strSQL
 
    DoCmd.GoToRecord , , acNewRec
   End If
   
End Sub

####################

Sub pass2(y As String)
  Dim store2 As String
  store2 = y
    If cmdprint.Visible = True Then
    
    Dim strSQL As String

   strSQL = "INSERT INTO TablePrint (CustomerTest) " & _
       "VALUES('" & store2 & "')"
   DoCmd.RunSQL strSQL
 
    DoCmd.GoToRecord , , acNewRec
  
   End If
End Sub

#################

Private Sub Add_Test_Click()
On Error GoTo Err_Add_Test_Click
  Dim clienttest As String
   Combo29.Visible = True
  clienttest = InputBox("Enter Test Data Please, seperate with commas")
 
     updatecombo29 (clienttest)
  
Exit_Add_Test_Click:
    Exit Sub

Err_Add_Test_Click:
    MsgBox Err.Description
    Resume Exit_Add_Test_Click
    
End Sub

#######################

Private Sub Add_Client_Click()
On Error GoTo Err_Add_Client_Click
  Dim clientname As String
  clientname = InputBox("Enter Client Name")
  Combo27.Value = clientname
  
    DoCmd.GoToRecord , , acNewRec

Exit_Add_Client_Click:
    Exit Sub

Err_Add_Client_Click:
    MsgBox Err.Description
    Resume Exit_Add_Client_Click
    
End Sub

###################

Sub updatecombo29(update As String)
  Dim change As String
  Dim lastrecord As String
  
  change = update
  
    Dim strSQL As String
    
   strSQL = "INSERT INTO Test (Test) " & _
       "VALUES('" & change & "')"
   DoCmd.RunSQL strSQL
    DoCmd.GoToRecord , , acNewRec
  
End Sub

[chembuff1982]

http://i147.photobucke t.com/albums/r301/chembuff1982/form.jpg

that's a picture of my form I took a snapshot of, not an advertisement. It's in my photobucket.

[chembuff1982]

anyone? Almost there, almost there, so close to being done.

[MMcCarthy]

Try this ...

Private Sub Combo29_Change()
Dim strTest As String ' define string Test to use for append

               strTest = strTest & "" & Me.Combo29.SelText & ", " 
      'append string from combobox

      ' to remove excess comma stored in append
   strTest = Left(strTest, Len(strTest) - 2)
      pass2 (strTest)
    
   cmdprint.Visible = True
   
   Me.Combo29.Requery

End Sub

[chembuff1982]

That doesn't work, I did try that a while ago, see the table, is updated by the form, but I'm trying to sort of refresh the new drop down box with the new selection. Would some form of acLast work? I got aclast to pull up a number for the new record, but I need a string value.

[chembuff1982]

So frustrating, it's the last button on the form. I have it sending to the table, just need ot repopulate my combo29 box which is for client tests. I want it to do it from the add test button. So if a current client has no selection or they are a new client it will allow them to enter test parameters.

[MMcCarthy]

So frustrating, it's the last button on the form. I have it sending to the table, just need ot repopulate my combo29 box which is for client tests. I want it to do it from the add test button. So if a current client has no selection or they are a new client it will allow them to enter test parameters.

Sorry I'm genuinely lost as to what you're trying to do :)

[chembuff1982]

my rowsource for combo29 (tests) is filtered by companyID number, however if we add a new test for a company, or add a new company, I want to be able to put that new test from add test in the Combo29 (tests) box before the form is submitted.

[MMcCarthy]

my rowsource for combo29 (tests) is filtered by companyID number, however if we add a new test for a company, or add a new company, I want to be able to put that new test from add test in the Combo29 (tests) box before the form is submitted.

So your problem then is when to trigger the requery.

How are new tests being added?

[chembuff1982]

The new tests are being added by a command button.
Add New Test

[MMcCarthy]

The new tests are being added by a command button.
Add New Test

Put the requery here

Private Sub Add_Test_Click()
On Error GoTo Err_Add_Test_Click
Dim clienttest As String
   Combo29.Visible = True
   clienttest = InputBox("Enter Test Data Please, seperate with commas")
 
   updatecombo29 (clienttest)
   Me!Combo29.Requery

And maybe put it in the Load event of the form.

Mary

[chembuff1982]

Nope both changes didn't work. I think we came upon a mystery of computer programming.

[chembuff1982]

I'm going to try to go to the local college to get help. It seems like nobody on here can figure the last part out. I called already, just going to talk to a professor who has a degree in computer science.