Form Combo Box - Search function not working within form

[rm.harper]

Hi,
I recently created a database to be used to store incoming invoices. We have 4 suppliers which supply the same material and wanted each supplier to have its own database.

I began by making a database which included all the key tables, forms, queries and functions I would need to then save 4 copies, one for each supplier. This just meant I would not have to repeat the process for each individual supplier.

The problem is all my data is external and upon importing it into the database it has made the search function (combo box) not return results.

I believe this is because:
1) The form was created before the records existed
2) The records were imported from an external source and not created using the form.

However, the way I have designed the form is so I can see all relevant information linked with a specific invoice. This aids in gathering information quickly about an invoice but also so I can input credit note and invoice query information linked to a specific invoice on the same page.

Is there anyway to remedy this?

Or use a similar search function that returns records in my form "Main View"

The last thing I want to do is have to create the form again, although I don't think this will work either as reason 2) states.

Any help would be appreciated.

Thanks
Ryan

 

[spikepl]

Before anything else, care to explain why you want to create 4 databases each doing the same thing? You are about to create a maintenance nightmare and get lots and lots of PITA

 

[spikepl]

Also, in the original thread about this DB that you seem to have abandoned (not a recommended practice and it will get you into the dog house pretty pronto), you never answered my question about how the invoice data got into the db. Someone typing them? Or have you automated that?

 

[rm.harper]

Before anything else, care to explain why you want to create 4 databases each doing the same thing? You are about to create a maintenance nightmare and get lots and lots of PITA

I know, its going to be incredibly hard to maintain!

I don't have an acceptable answer to this. My only answer is simply: Laziness. Using one database for ALL suppliers would mean I would have to include a table for suppliers and include a look-up field in my invoice table to include which supplier this invoice has been received from (as you will know).

On average I receive 100+ invoices a month from EACH supplier and the time it would take to specify the supplier for each invoice is, in my eyes a worse nightmare than the maintenance aspect :banghead:

It would be much simpler (maintenance issues aside) to simply click one button to open a supplier specific database and continue to input information from a pile of invoices.

I understand this is TERRIBLE Access practice but for what this database will be used for I'm hoping to skate under the radar of database karma ha

 

[rm.harper]

Also, in the original thread about this DB that you seem to have abandoned (not a recommended practice and it will get you into the dog house pretty pronto), you never answered my question about how the invoice data got into the db. Someone typing them? Or have you automated that?

I have not had time to check my previous thread regarding this DB - this was a quick posted thread for help with a specific form problem.

The original thread will be tidied in due time.

In answer to your question - The previous months of the year running up to todays date were/will be imported from my original excel format. Any invoices from today onwards will be inputted, hopefully, manually using my form "Main View".

 

[spikepl]

"Database karma" will bite your derriere, because it doesn't care.

I don't expect any other reader here will support your misjudgment.

You'll get there yourself eventually, despite rejecting the shortcut offered here. Now this I suspect most experienced readers will support me in saying.

 

[spikepl]

SO you got around to saying "manual input". Why is this input manual? That just seems a waste of human resources.

Also, when you ask questions then you should complete your previous questions before starting up new ones. Epsecially when the answers have a bearing on your "new" subject. (That thread is here: http://www.access-programmers.co.uk/forums/showthread.php?t=279389 )

 

[rm.harper]

SO you got around to saying "manual input". Why is this input manual? That just seems a waste of human resources.

I have put some thought into the whole 4 vs 1 DB issue, and I fully agree. But at the moment I'm happy with 4 DB choice. I wont be in time, I understand that but the simplicity of the database at this stage justifies my choice - in my eyes

The entry of records has to be manual as they are also checked for correctness while being inputted. I was unawares to the capability of automation using PDF sources. But at this stage they are hard copies received by post. Something to look into in the future maybe.

Also, when you ask questions then you should complete your previous questions before starting up new ones. Epsecially when the answers have a bearing on your "new" subject. (That thread is here: http://www.access-programmers.co.uk/forums/showthread.php?t=279389 )

They have a bearing because they relate to the same database but in different versions. At this time my original thread and its questions are on hold as the solution of the issue in this thread impacts the longevity and need for the old thread. I have politely thanks users who have replied and was unawares to the replies it has gained. But leaving the subject of forum practice for now if you don't mind, as its off topic in relation to this thread. Thank you for pointing the replies ive received.

Do you have any ideas to remedy the search function on the form? Ive tried a query which successfully returns the fields I need to view when an invoice number is specified but the layout/format does not lend itself to the intended use i.e I would like it to look like a form I can fill in.

Thanks

 

[Minty]

I really don't understand the reluctance to use one database here.
If having to select a supplier on the form to input the invoice is such a drag, why not open the form based on choosing a supplier from a menu beforehand, then you would simply have a default.

What happens when you need to add another supplier? What happens if you want to compare pricing?

I assume you wouldn't dream of having a bank account per supplier to pay them?

 

[Uncle Gizmo]

I'll put in my two penny worth here. You are getting some good advice and your own stated reason for not taking it is laziness! Why do you think anyone now would want to help you, seems like I would be wasting my time! I would much rather help someone who would at least listen and learn.

 

[rm.harper]

I really don't understand the reluctance to use one database here.
If having to select a supplier on the form to input the invoice is such a drag, why not open the form based on choosing a supplier from a menu beforehand, then you would simply have a default.

What happens when you need to add another supplier? What happens if you want to compare pricing?

I assume you wouldn't dream of having a bank account per supplier to pay them?

I didn't know I could set 4 forms each with its own default supplier. That would solve the problem of the 4 separate databases! Thanks!

This database is literally being used by myself to hold invoice information for viewing by me only and was originally created for the sole purpose of having the ability to search by InvoiceNumber to show all fields relating to an invoice, add or change records in those fields and close. The only other use it to print a monthly statement for each supplier. No comparisons are needed using this database. Its only functions are the ones above.

I'll put in my two penny worth here. You are getting some good advice and your own stated reason for not taking it is laziness! Why do you think anyone now would want to help you, seems like I would be wasting my time! I would much rather help someone who would at least listen and learn.

Ofcourse. But from my point of view pretty much all post's relate to a separate topic that was not raised by myself but instead by people trying to 'help'. All I wanted to know on this thread was answers in regards to my search function. Not a total roasting from other members about how I choose to arrange my information. To then get into some what of a debate about how Im doing it wrong. I understand I'm doing it wrong but that's how I want to do it. Im rejecting advice that has no relevance to the question risen in this thread because its advice that I already know will cause controversy with other members ( like it has )

If Ive upset anyone then im truly sorry but instead of actually getting help on the subject of my search function ive wasted MY time replying to posts from everyone who have felt the need to tell me HOW to do something that is not up for discussion.

I appreciate the help in regards to the arrangement of information from 4 different suppliers and can assure everyone I am fully aware of how it SHOULD be done. Hence my confident decision in not doing it in the advised manner. On the other side of your 2 penny worth why can people not respect my choice to do thing my way. Its help I did not ask or have any need for at this time. I simply want help on getting this search function to work.

I cant believe its been taken this far. A debate over how I choose to do something over any helpful suggestion based on the ACTUAL thread.

 

[rm.harper]

What a waste of everyones time. Thanks Minty for your suggestion.....this unlike others had an explanation behind it rather than a total disagreement and 'b**ch fit' about how I choose to work.

Stuck up IT people with no social skills outside a chatboard/forum is pathetic practice.

P.S Ill remove myself from the forum....save you wasting your precious time. Jokers!

 

[Minty]

I didn't know I could set 4 forms each with its own default supplier. That would solve the problem of the 4 separate databases! Thanks!

You don't even need 4 forms - one form with the supplier chosen BEFORE you open it will simplify things even further.

 

[rm.harper]

You don't even need 4 forms - one form with the supplier chosen BEFORE you open it will simplify things even further.

Ill look into it thanks!

But it still leaves the original question unanswered.

I will continue on my own, this is just frustrating getting a straight forward answer.

Thanks again Minty.

 

[Minty]

Okay - for my 2p worth I don't think your form problems are due to the imported data,
I suspect it's to do with either how you are pulling the data into the form or how the data was actually imported.

Without seeing a sample of the data / tables and the form it would be difficult to comment further.

 

[rm.harper]

SOLVED - thanks

 

[mjdemaris]

I agree with Minty, we need info on your setup. You can do it with one database, setting up your tables shouldn't be too hard, and with one form. You can create a datasheet form based on a query that selects the data you need and display it as a subform.
If using a combo box, make sure it is bound to the field you are searching in, or you could use a text box and create a SQL statement (in VBA) to search as you type.
Post table structure and code you are using for us.
Thanks.