Importing Data ??

[BLONDIE2]

I am a newbie at Access2000 (although experienced with computers and some work with Excel) and need some help please.

I have a Access database complete with Forms,Reports, Queries, Switchboard, and Tables. The users have to fill in (repeatedly on different forms/reports) Names, Record numbers, etc. There is an ODBC database available which contains all of this data that I would like to ACCESS (PUN INTENDED, forgive me).

PROBLEM: I can't seem to pull this into Access as I need to (I've done it in Excel, but need to expand to Access for more usefullness).

How do I set up criteria or request for paramater values---something like this...Where STATUS="A" and REC_NUM = [#########] (where user inputs a nine-digit record number). Once it pulls THIS (and ONLY THIS) record, I need it to populate approx. 15 fields that are associated with this record number. If I can put this on one page(like a spreadsheet???), I can link it to other pages/forms/reports (I think).

Eventually, I want it to:1--automatically ask for this number at startup AND
2--have a command button to PRINT, Save records, Clear input fields, and ask for another record number.

Can anyone help me get started on this QUERY????
Thank you so much!

BLONDIE2

 

[mdmilner]

My suggestion to self-teach is this:

Create a query based on a local table (in the Access mdb you're working in) - use the QBE wizard (query by example) to make a base query.
While in design mode, in the 'Criteria' row, pick a field (column) that has text values in it (in the table it's based upon) and type in a valid text value that matches one or more entries.

Don't add any quotes. As you exit the field it should put quotes around it automatically as you leave the "cell".

Run it. It should only return valid matches for the parameter you entered.

Basically, this is precisely the technique you will use later.
You can use multiple parameters - put them all on the same row. Items on different rows become "OR" parameters. Match this OR that. You can use SQL view to look at the statement.

The next question is. Do you just need a query or do you need this to show up on a form/report? Forms and Reports use SQL Queries as their 'recordsource'. Technically, whichever you end up using/needing - what you'll use looks and works exactly the same. The only difference is how and where you set it up.

Automatic: Each form/report has a series of events that happen to it as they are opened. Things like "Load", "Activated", "Open", "Focus". To make something automatic, within Access, the general trick is to make one or more forms open automatically on startup. And, in the "Load" event for the form, you put VBA code to do whatever actions need to be done.

And, pretty much, there are no limits as to what you can throw into the Load event, but, being programming, it all has to be done correctly with error trapping, etc.

ODBC Connections and other databases. First, what kind of database is it.
Access includes built-in tools to link to a variety of table type objects - you can also open connections from VBA code. Lot of options, but the technique to use depends on a LOT of design details to your specific mdb.

My recommendation at this point is to start small. And ask your questions as separate posts/threads over the course of several days or weeks as you develop the skills to understand what you're looking at. Being able to do queries is a prerequisite to being able to set them up as the recordsource for a form/report. Being able to write VBA code is a prerequisite to building additional automation into Events. It would be exceedingly difficult for us to give you detailed examples without you providing a lot of information.

Mike

 

[jmersing]

I agree with Mike but I would just offer one more suggestion. If you have been able to create a form Blonidie2, you may want to try and add a control called a combo box to your form. A wizard will run and it will ask you for the recordsource of your form. It will then give you a few options on how to retrieve a record. If you choose the option:

"Find a record on my form based on the record I select", and then follow through the rest of the wizard you will be left with a combobox on your form. If you add some different controls called "text boxes" you can select a control source for each one. This is where you would select the fields that have the data relevant to what is returned in your combo box. You might want to try it just to see how it works, but as Mike said this could be difficult to do back and forth on posts.

Maybe we could do it in steps, can you describe the components of the db in a little more detail?

 

[BLONDIE2]

Thank you BOTH for your input.

I'm sorry for the delay in getting back, but have been sidetracked by Bonnie and Charley.

I'll try to do these. Thank you so much.

 

[BLONDIE2]

OK--I'll try this again...

I have connected to a LARGE database via ODBC. I want to pull ONE (and ONLY ONE) Record number (REC_NUM) into a form and populate the form with info from this one record.

How do I get it to pull JUST this one record--it wants to download the entire database??

Thanks

 

[BLONDIE2]

Can anyone give any help on this?

I want to pull ONE (and ONLY ONE) Record number (REC_NUM) into a form and populate the form with info from this one record. (I have already connected to a LARGE database via ODBC. )

How do I connect the query to a form or report? I need to pull JUST one record (by user input of record_number)--it wants to download the entire database each time??

Thanks