Desperate for help

[mbentle]

I'm hitting a deadline of Monday afternoon and I really need some assistance.

I'm trying to incorporate the search.mdb sample database (I attached it for easy reference) into a project I've been working on. Unfortunately, I really don't understand how it works and need some clarification.

I'm trying to create a database that stores the loan options for a lender. I work for a real estate company and they need to be able to search through many many different loan options to find out who can offer the best rates for low-volume loans (i.e. loans that are not obtained frequently and have specific restrictions).

Initially, I wanted to create a form that would allow the user to simply input what features are applicable via check boxes and then the appropriate result would be spit out. But, given that there are 30-50 fields and that I'm a complete n00b to database design and implementation, I decided that was WAY over my head.

So, now I would be happy with just being able to search the 5 top fields and then display the complete details of the selected option as the example db shows.

Here's my problem. I'm using look up tables to establish specific options. The employee who is entering the data isn't the most reliable and to ensure that some of the info is correct, I have to limit the options to check boxes on the input form. To establish that, I created a few look up tables. Because the sample db is a single table, I'm having problems implementing it.

Can anyone explain (in layman's terms...I'm a n00b) how this sample db is created? I've tried de-engineering it as best I can and I cannot understand how it works. If I understand it, I think I can get it to work.

Also, I'm out of ideas on the best way to create data entry forms. I've seen a lot of example but I wanted to find out what everyone felt was the best method. It needs to be as absolutely dummy proof as possible.

I really, truly appreciate the help. I'm up against the wall and I'm praying for some assistance.

Thanks

 

[richary]

Basically the database you posted uses the OnChange event of the search textbox to requery the large listbox (via a textbox called Search2). The listbox recordsource is an SQL query string which uses the search2 textbox from the form as is parameters in the WHERE clause.

Obviously, I haven't seen your requirments in detail, but if you want my opinion, I'm not sure how applicable this example db will be. I would imagine a loans db would have predominaltly number-based criteria (percentages, payback periods etc.) This example works with strings.

I realsie that you are up against it in terms of time. If you have any questions, I will have to defer to others on the forum as I am basically out of the office for teh rest of the day from now. I just saw your plea and tried to do what I could in the time !

Good luck!

Richard

 

[The_Doc_Man]

I'm up against the wall and I'm praying for some assistance.

Been there, done that, bought that T-shirt. But you are closest to the problem. Expecting timely help from we who do not know your company rules on things is an unrealistic request. This forum is usually a tickler & teaser forum - we point in directions, we offer ideas, we reference Help topics. But the only way for us to do more than that is to have so much info that it is a waste of YOUR time to compile it. Given that most of us have employers who expect a modicum of work out of their people, probably we don't have the time to go through that thorough a compilation.

Also, I'm out of ideas on the best way to create data entry forms.

Now, there, I can offer some more direct thoughts. Here are the basic rules on putting together forms...

1. Be consistent. (Decide on a look & feel ahead of time, stick to it.)

2. Be informative. (Show folks what they need to know to do the job.)

3. "Easy to read" means "Easy to use." (Self-explanatory.)

4. Show, don't tell. (I.e. let people see things for themselves.)

5. Do, don't ask - unless necessary. (I.e. if it is a form for making LOTS of changes, don't ask permission for every change. One OK fits all, unless there is a step with a special business rule about confirmations.)

6. Dialogue, not monologue (I.e. Windows is INTERACTIVE - so where there is an appropriate moment to ask a question or give a choice, do so. And now you see that good forms strike a balance between #5 and #6)

7. Let the driver drive. (I.e. don't make assumptions that once the user looks at record X s/he is going to post record Y. Record X might have been of interest simply to see what had been done so record Y could be entered, OR it could be a check for information that needs updating in record X. Or maybe a third record, Z, is being examined for data consistence. Don't assume anything. Give options and trust the user to choose.)

On the other hand, you were worried about making it safe for inexperienced users... therefore, the corrolary of #7 is to put up only the options that apply to the situation, not all possible options that could ever apply to any situation. Disable action buttons sometimes. Make 'em not visible and not enabled. Move them around on the form if you have to. If you are seeking to idiot-proof a form, don't let the idiots do anything they shouldn't be able to do. Then, let your boss test it. (If you like your boss, let his/her boss do the testing instead.)

 

[mbentle]

Richard, Thanks for the brief explanation. It explained things a little bit to me. I didn't realize the requery.

Doc Man, building this input form is something I think we can evaluate and develop on the fly, but I really do appreciate your insight because it has given me reason to change a few things.


In regards to a more specific issue, where I'm at right now, I've imported the search database into my existing one and I'm trying to adapt it as best I can. I'm replacing the existing fields in the listbox with ProductName, ProductDescription, LenderName, and DueYears. Then on the right side, I'm adding details.

In the VBA, I've replaced the rs.FindFirst "[CompanyID] = " & Str(Me![QuickSearch]) with rs.FindFirst "[ProductID] = " & Str(Me![(QuickSearch])
From what I've seen, thats all I really need to edit because it's the only field in consideration, everything else is edited via SQL in the ROW SOURCE of the listbox. Is this correct?

I'd love to attach a smaller version of the db, but it's too big for the attachment regulation for the forums.

One problem I'm running into is the listbox isn't correctly displaying the information and I think it might be in my SQL. I was trying to duplicate the SQL from the search.db when I noticed that the build button for the ROW SOURCE of the listbox had a second query that wasn't listed. The SQL doesn't refer to this query but it is there nonetheless. I found this terribly confusing. But, here is my SQL call and the original. Maybe someone can point out what I'm doing wrong. When I go to run the form, I get the field headers but no details.

Here is the original line:
SELECT tblCompanies.CompanyID, tblCompanies.Company, tblCompanies.Address, tblCompanies.Town, tblCompanies.PostCode, * FROM tblCompanies WHERE (((tblCompanies.Company) Like "*" & Forms!frmCompanies!Search2 & "*")) Or (((tblCompanies.Address) Like "*" & Forms!frmCompanies!Search2 & "*")) Or (((tblCompanies.Town) Like "*" & Forms!frmCompanies!Search2 & "*")) Or (((tblCompanies.PostCode) Like "*" & Forms!frmCompanies!Search2 & "*")) ORDER BY tblCompanies.Company;

and here is my line:
SELECT tblProduct.ProductID, tblProduct.ProductName, tblProduct.ProductDescription, tblLender.LenderID, tblProduct.DueYears, * FROM tblProduct WHERE (((tblProduct.ProductName) Like "*" & Forms!frmProductListing!Search2 & "*")) Or (((tblProduct.ProductDescription) Like "*" & Forms!frmProductListing!Search2 & "*")) Or (((tblLender.LenderID) Like "*" & Forms!frmProductListing!Search2 & "*")) Or (((tblProduct.DueYears) Like"*" Forms!frmProductListing!Search2 & "*")) ORDER BY tblProduct.ProductName;

This is the error I'm getting:
Syntax error (missing operator) in query expression '(((tblProduct.ProductName) Like "*" & Forms!frmProductListing!Search2 & "*")) Or ........

it just goes on through the rest of the SQL line.

I know it's hard to get without having the db in front of you, but if anyone has any insight into what I'm doing wrong then I greatly appreciate it. The original example has only one table and I'm working with multiple which might be causing part of the problem. Here's what my table strucdture looks like

tblProduct
[PK] ProductID
ProductDescription
ProductName
DueYears
[FK] LenderID
etc

tblLender
[PK] LenderID
LenderName
etc

The last thing, I can't figure out how the details on the right are displayed. I don't see any SQL referring to the list box and I don't see any builds. How is that info being displayed?

I hope thats clear. I'm trying to think this through as I type it so if it is sporatic, I apologize. I really appreciate the help.

Michael

 

[mbentle]

DocMan,
I just re-read your post (I read it quickly the first time). If I'm going beyond the scope of the forum with my requests, please just let me know. I'm not trying to over shoot with my expectations.

I plan on making this work regardless of how long it takes me. It's one of those things that I have to finish now that I've started it, even if it doesn't impact anything in the business sense. After tonight, time probably won't be as essential (unless I get an extension from the boss which I'm not expecting).

Either way, I really appreciate the assistance.

 

[mbentle]

Ok I got the quick search 'working' even though I have a few issues still pending. I'd love to post it but even after I zip it, it's well over 600 kb (and thats having broken it down considerably). I heard there was a way to compress it but I'm not sure how to do that. If I figure it out, I'll post it so you guys can see what I'm talking about.

Thanks for the help.

 

[The_Doc_Man]

If I'm going beyond the scope of the forum with my requests, please just let me know.

You can ask for anything you want on the forums, but not everyone has enough time to do in-depth analysis. I also have a second problem in that my site does not allow me to download from general internet sites that don't have specific security capabilities. Other members probably have similar if not identical restrictions. 'tain't your fault, but it is the nature of the forum experience with an international user base.

The last thing, I can't figure out how the details on the right are displayed. I don't see any SQL referring to the list box and I don't see any builds. How is that info being displayed?

If you built the list box (same applies to combo box) using a wizard, Access can sometimes build a "hidden" query for you. The query would be referenced in the .controlsource of the control. Sometimes the details come from these hidden queries.