(Hopefully) simple question!!

[jameslamb]

Hi everyone,

First let me say I am a total newbie to access (save for a few dabbles here and there) and would really appreciate some advice! Not sure if this should be in this forum or the queries one - sorry!

I am creating a basic database for work to keep track of the movement of equipment on loan to our customer base. I have inputted some data and created a query to collate it. The problem I am having is with the form I have created to view the info in the query. I have created a combo box for the customer field and this works ok but only the data in this field changes, the other fields stay the same. I presumed that when you select a different customer, the other fields would display the various info for that record.

I suspect that I am missing code somewhere, and would be grateful if someone could point me in the right direction. I fear I may have jumped in the deep end here with too little knowledge so if that is the case I would be grateful if someone could point mw to a good tutorial as I have been googling for the past hour or so and can't seem to find anything appropriate!!

Thanks again in advance,
James

 

[georgedwilkinson]

You haven't given us much to work with so here are a couple of thoughts:

Is your combo-box bound? If it is, you will be updating the current record, not searching the table/query.

Is there any code behind your combo-box? Look in the properties of the combo-box on the Events tab. If not, and if your combo-box is not bound, your combo-box won't do anything. Take a look at this to resolve that: http://www.allenbrowne.com/ser-03.html

If that is too rich for your blood, did you know you can use built in Access tools to do most filtering tasks? Just right click on the bound field you want to filter by and there will be a box you can type your filter criteria into. Something many people don't know is that you can type wild-cards into the filter box, which allows you to filter on partially known information (like: I know the dude's name is "Fred" so I type "Fred*" or "*red*" in the filter box).

If you want to try and it's too much for you, give us a yell.

 

[jameslamb]

Thanks very much, I will have a look at that.

Cheers,
James

 

[bulrush]

The link given above is accurate but is missing one thing. In the cbxSearch_Enter event put this:
cbxSearch.Requery

Otherwise your combo box used for searching for a record won't show record changes or new/deleted records.

Oops. The code in the link above is NOT accurate and does not account for an Access bug first seen in Access 97 and still a problem in A2003. Here is the code I use in cbxSearch_AfterUpdate.

Dim Criteria As String
Dim MyRS As dao.Recordset
dim procname as string

on error goto MyError
procname="cbxSearch_Afterupdate"
me.requery ' to account for Access 97 bug.
cbxSearch.requery  
Set MyRS = Me.RecordsetClone
  
Criteria = "[SSNum] = '" & cbxSearch & "'"
MyRS.MoveFirst
MyRS.FindFirst Criteria

If MyRS.NoMatch=true  Then
    MsgBox "Could not find a match for " & cbxSearch
Else
    Me.Bookmark = MyRS.Bookmark
End If
MyRS.Close
set MyRS=nothing
exit sub

MyError:
Call DispError(procname)
exit sub

If the current record is changed, this code will save the changes before pulling up the desired record.