Search Problems

[Ir8munk33]

Hi,

I'd like to see if anyone can help. I'm not a complete beginner in access but i have been having some problems and i think it may be that i dont know some better techniques for what i am doing...

I have a fairly simple database which i am creating for the storage and reporting of our returns within our company.

I have 1 Main table which i am storing all the refund information in.

I have 3 forms, 2 of which use a search function to bring up a record:

Form 1 - Is a returns entry form. The user fills in all the required boxes and clicks save, which saves it as a new entry in the table.

Form 2 - Is used for when a returned item gets back to us. The user needs to be able to bring up a record, add the extra details and save it again.

Form 3 - Is used when the returned item has been processed. The user needs to be able to bring up a record, add extra details and save it again

I cant seem to get a search function to work for the forms 2 and 3. i want to be able to use the order number to find the required returns record and add more information to it.

Help please!

Thanks

Chris

 

[MStef]

If you have got 1 table, you dont need 3 forms.
It's enough 1 form.
Send a short example of your DB. (Access 2000, or 2002, or 2003, zip).

 

[DCrake]

If you are simply using the order number to filter the form then it may be easier to place an unbound combo box on the top of the form that has the order numbers as it row source. Then once the user has selected an order no use this to apply the filter to the form.

 

[Ir8munk33]

The problem is that there will eventually be a lot of entries in the database, and scrolling through a list of hundereds of order numbers could be a bit of a pain in the neck for a system i am building to speed the whole process up (we currently use an excel spreadsheet with 24 columns and currently 560 rows!)

 

[DCrake]

Combo boxes have auto complete functionality and will actually speed up the process.

 

[Ir8munk33]

And in reply to Mstef, i need the 3 forms, as they are used by different people. customer service to add a return, the warehouse add the details when the return has arrived back with us, and the accounts department fill in when an item has been refunded

 

[Ir8munk33]

Right i have got my nice shiny new combo box with the order numbers in it, but how do i prompt for the data to be pulled from the table?

In the form i bring up the customers surname and any additional notes in an uneditable text box to show that the right entry is selected and i have a number of extra boxes for the user to fill in, and finally a save button to save the changes.

I feel like a bit of a noob with this one, but unless you've done it before, its difficult to know how!

 

[MStef]

I think your tables not normalized well. If you need 3 forms, then you need a 3 tables.
Learn something about DATABASE NORMALIZATION.

 

[Ir8munk33]

Lol, i'll have a look into it, but i think you should do a search in the forums Mstef.

Search for : Mstef DATABASE NORMALISATION



PS, everything is in one table so that i can just glance over the table if needs be and edit what i need. Might think of splitting it up in the future though, but i do also have a fourth form, which allows the user to edit every peice of information in the table, one entry at a time (which i shall be using this search function for too)

 

[DCrake]

I am assuming that you are using a bound form, in that case on the AfterUpdate event of the combo box

Me.Filter = "[FieldName]='" & Me.ComboBox & "'"
Me.FilterOn = True

 

[Ir8munk33]

Thanks, i'll have a go

 

[Ir8munk33]

Hmm, that brings up a nice little box for me to enter the order number into, but no luck on bringing up any data...

I have attached the database to give you more of a clue into what i'm going on about.

 

[Kaybaj]

create your combo box using the wizard and choose the option of "find a record on my form based on what i select in my combo box" you should get some where with that

 

[DCrake]

It seemed to have an issue with the FilterOn event so I have supplied an alternative solution for you. Check the returns 2 form.

 

[Ir8munk33]

Thank you, that is just what i needed!

 

[JamesMcS]

What's the deal with 3 tables 3 forms? You can have 1 table and 3 forms, or 3 tables and one form. The beauty of Access....!