access 2003 forms help PLEASE!

[patti26505]

I have made several tables for my database, and created a form for my co-worker's easy use. In my form there is information about clients that will not change i.e. DOB, Name, SS#, etc..... And in my form are a few subforms that contains information that will change. I need to be able to have some way of searching the unchanging info and popping it into the form. I have made a query that includes all the unchanging info (and shows it in datasheet form), but I can't figure out how to click and insert into the form so new information can be added to the subforms about the clients. Am i clueless about a query command perhaps? I have put a command button on the form linking to my query and it works fine, I just cant figure out some kind of easy insertion to the form. I'm not even sure i'm asking this question right. Any help would be wonderful! Thanks so much!

[Megalog]

This 'unchanging' data... is there a unique identifier for each Client? For this example, I'll use the SS#, but change it to fit your needs..

Let's assume your SS# field is named txtSSNumber.
You'll need to convert it from a Text control, to a ComboBox. Then what you'll need to do is set up the combobox's rowsource to query that underlying table's SS# field, by running the query builder on it, and only selecting the SS# Field. Set the criteria to "Is not null", and sort it by Ascending.

Now, in the 'On Change' event of this combobox, select 'Event Procedure', and then insert the following code:

    Dim rst As DAO.Recordset
    Dim strSearchName As String

    Set rst = Me.RecordsetClone
    strSearchName = str([B]Me.txtSSNumber.Value[/B])
    rst.FindFirst "[B]SSNo[/B] = " & strSearchName
        If rst.NoMatch Then
            MsgBox "Record not found"
        Else
            Me.Bookmark = rst.Bookmark
        End If
    rst.Close

Replace the 'me.txtSSNumber ' with the name of your combo box, and the 'SSNo' field with the name of the actual field in the table/query you're searching for.

Now, you can open up the drop down box, select a SS#, and upon this 'Change', it will seek out a matching record and take the whole form to it.

This works well for small-medium sized databases.. but if you happen to have thousands of clients listed, we can use a different method to actually search for what you enter, instead of generating a mile-long list to choose from.