Search and Fill In

[atcstech]

Okay, So i looked at some of the search functions that some, obviously, excellent programmers put together.
My work has put me in charge of building a new database instead of doing everything with pen and paper.
The problem is that I am totally clueless when it comes to anything to do with programming.

I have, attached, a DB i am working on.
I was hoping someone would be able to help me get a couple things together.

I have a form already made called "Customers"
I have the design already done, but no idea how to code it. The search form is completely nonfunctional.
I would like to be able to type in a name and get the results to fill in the rest of the form, if multiple results, i would like to be able to just click the right arrow at the bottom.


Also, I cannot seem to figure out how, in the "Computer" form when I put in the Service Order Number it will feed back so if I pull up a Customers name in the Customer form it will have the combo box with all the service order numbers listed so i can double click the service order number and it pull up that order in the Computer form.


Any help would be appreciated. I don't want anyone to do it for me, but i can't seem to find any tutorials that explain what to do for a complete non-programmer. The templates that I have found, I can't figure out because i don't know what they are doing, etc.

Thanks in advance, and I hope someone can point a finger.
Attached you will find my DB so that you can hopefully understand what I am rambling on about.

 

[atcstech]

can anyone help? I'm still stumped.

 

[spasticus]

hey.

i made a few changes to you database. im not sure if its what you wanted but i hope it helps.

 

[atcstech]

hmm,

that doesnt seem to work.
When i type "Tom" into the search box, it comes back with no listings.

I am at home now, but I will spend some time looking at it tomorrow and see waht you did to actually make a functional search and see if i can maybe figure it out.

Thank you for trying though!

 

[spasticus]

in the database i uploaded there was no record with the name tom - if you using the same, un-changed one then thats why. access will take you to a new, blank record if it cant find a record.

 

[atcstech]

Thank you for your reply.

In the Customer table there is a field called ContactName
There are two names in there with the first name being 'Tom' and a third with the name I believe is 'Jimmy'

Maybe my query from the original post was stated wrong?

In the search I want it to search multiple fields. The name part is awesome and I am sure once we get just the name search working I can figure out how to get it to search multiple fields. But for now lets concentrate on one area.

When I do a search i would like for it to search the ContactName field in the Customer table. I see in the script that you are searching CompanyName or so it seems (i don't know that much about the script)

I tried changing that to ContactName and it still came up with nothing. Can you tell me where you are searching that has no names in it?

 

[spasticus]

your right it is searching CompanyName. just change it to:

Me.Filter = "[ContactName]=""" & Text31 & """"

 

[atcstech]

Okay,

ya i changed that before and THOUGHT it didn't work, but it does.
But to an extent.
I have to type in the full name

eg - if i search for "Tom" i find nothing, if I search for "Tom Thomas" I find the listing.

Is there anyway to add to the script an automatic wildcard (*)?

thanks again, i really appreciate your help.

 

[spasticus]

try this:

Me.Filter = "[ContactName] like '*' & '" & Text31 & "' & '*'"

 

[atcstech]

that works perfectly!!

How would I write

If
table "Customers" field [CustomerID] = table "Computers" field [CompOwner]

Then
DoCmd.OpenForm "Computers", acNormal, , "[CompServiceOrder]=" & List44.ListIndex + 1


that would work if one customer had multiple computers, right?
I will try to look at the code you have given me so far and see if I can come up with something. Thanks again for your input and I hope others can find use for this!
can I ask where you learned to code? was it a book or on the web?

 

[spasticus]

i learnt allot from trial and error. i am doing a course at the moment which involves access and VB - that helped a bit. allot of the time i picked stuff up off the internet or forums; when i couldn't solve something myself i asked for help.

to try and answer your other question ... which table, if either, is your form related to? i mean, which is the recordsource?

 

[atcstech]

I was working on the relationship thing and it was confusing me.

Basically we will input the customers name in the Customer form which writes to the ContactName that we were previously dealing with.

When we go to the Computers form i would like to be able to type in the name of the customer or have a pull down of all the customers and that writes to CompOwner

So basically, the relationship is between CompOwner of the Computers table and ContactName of the Customers table, but the data is actually written to the ContactName of the Customers table.

Blah, this stuff gets a lot more complex then I thought, because I already have the relationship semi setup, and its a drop down but when I have 500 customers, thats gonna be a huge drop down to scroll through... maybe I will add a button the Customer form so that I can open up a new Computer form with the CompOwner and Phone areas already filled in.

My brain is swelling and pink goop is coming out of my ears...

 

[spasticus]

ok now you've gone and confused me. if upload you database again i will take a look and try and help you out.

 

[atcstech]

relationships

good, now we're in the same boat and it's sinking ever so slowly

lets see if I can 'splain this better.

Table/Field
Computer/CompOwner
Customers/ContactName

I want to type in the ContactName with the Customer Form and when I go to the Computers Form i want a drop down that has the ContactNames in it.

I setup the relationship between the ContactName and the CompOwner and it just reads the CustomerID number, not the ContactName text - so I dunno what i did wrong.

 

[spasticus]

hold on a sec, i think im actually with you!

take a look at the Computers form and Customers form and tell me if that's what you wanted.

if it is i'll explain what i did. quite simple actually.

-- i realise there's no database attached. i posted before attaching it then i closed the database without saving the changes. so ill do them again and hopefully attach the database on the next post.

 

[spasticus]

here you go... and i just realised i cant attach a file.

heres the link to it

ATCS

 

[atcstech]

It's cool,

i got 5 computers to work on, so i dont have time to spend on the db right this second anyway.

thanks though!!!!

 

[atcstech]

that's really friggin cool!
i will spend some time this afternoon and try and wrap my head around the code.

Do you know of anyway of setting the autonumber to start higher then 1?
i want it to start around the 6 thousands.

 

[spasticus]

i do now... look at this

 

[atcstech]

Heh,

as soon as I made that post I realized I was retarded and sitting on a forum asking redundant questions. I was reading that thread when i came back to make a post and let you know I decided to go with just typing the number in myself, makes more sense in case I decide i need to skip a number for any reason.

I really appreciate your help.