Can I have input in a form from one db and output into another? 1

[ApparentlyInept]

Ok folks this is what I'm trying to do and its kicking my butt. I'm trying to set up a database to track equipment. I have all my information for the equipment in one database but would like to set up a sort of check in/out form that puts that information into a new database so I can keep historical documentation of who has signed out the information and when. This may not even be possible but if anybody can help me I'd really appreciate it.

 

[jedel]

This is not a really difficult task. If you have the "Archive" Database already created, just make an append query and run the query based on the information you selct from your form.

I'm assuming that you have a method of logging users of each record, or equipment.

Let me know if you are having troubles with the append query.

Cheers

Dean

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[ApparentlyInept]

That wasn't exactly what I was meaning....or maybe I'm just looking at what you said the wrong way. I'm wanting to be able to figure out how to input into a form a way to pull the information that I need from Table A: (ie. Serial Numbers, ITAMS Number, Nomenclature) be able to add information into the same form with fields from Table B: (ie. Sign Out Date, Signed Out By) and have all that information posted into Table B. With multiple occurences of the same Serial Number possible so I can keep a sort of living accountability of who has or has had my equipment and when. I don't even know if this is possible.

 

[jedel]

Ok,So if I''m understanding you want to grab peices of infor from two tables and log them into another table in a different DB?

Have you thought about the "DLookUp" Function?

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[willir]

You first need to "link" the tables in question. From the menu, "File" -> "Get External Data" -> "Link Tables"

You can then treat the linked tables as if they are part of the current database. This includes creating forms, running queries, etc

 

[ApparentlyInept]

jedel/willir-

Both tables are in the same database file guess I didn't do a very good job of communicating that huh. Sorry for the confusion. But I'm wanting to pull some information out of TableA because TableA has my equipment list. TableB has the other fields that I want to fill in (ie. Signed Out By and Sign Out Date). I am wanting the info that I pull into the form from TableA to be posted into TableB along with my inputs for who/when the equipment was signed out.

 

[willir]

Well, this is good.

Have you tried using the query builder to create your append query?

From the menu, "Insert" -> "Query"
Select design view
Add your "From" or "Source" tables
Add the fields (columns) you want to capture.
Set any restrictions you want to use (WHERE clause)
To change this to an Append query, from the menu, select "Query" -> "Append query" (or "Make Table")
Select the "To" or "Destination" or "Target" table.

For each of the selected feilds or columns, choose the appropriate field in the destination table.

Run the query.

You can also do the above

 

[jedel]

I have one question, how do you know who signed the equipment out? the reason I ask is because that will be the search criteria for you query.

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[ApparentlyInept]

jedel/willir-

People have to come to me to sign the item out. And I physically hand it out to them. I tried the append query and it didn't do what I was wanting unless of course I just did it wrong. The only things I'm wanting in TableB are items that have been signed out.

 

[jedel]

OK, I think I may have an idea of what you want. I'm just trying to picture the actual task you are doing. Firstly, on your screen you have a db that you will bring up with all of your "Customers". From there you want to select what equipment they are getting and then record that data into a a third table?

If this is the case, I reckon I have it nailed

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[willir]

The only things I'm wanting in TableB are items that have been signed out.

It sounds like you really want a front end on this where you sign out the equipment. In this case, you want a form to do this.

Create a form based on the "destination" table.
Change the various fields linked to the "source" tables to combo boxes where appropriate. By entering the data into the form, you update the "destination" table with the selected info.

Hint: To do the above, open the form in design mode, and make sure you have the "Properties" window is open. (From the menu, "View" -> "Properties")

To change control from a text box to a combo box, select the text box, then from the menu, "Format" -> "Change to" -> "Combo box".

BTW, I re-read your post, and realize this is what you want - CheckIn and CheckOut.

A couple of points...
- By databases, I think you mean "tables"
- Instead of moving data from one table to another, you can use a status field on the table. The status could refer to "CheckedIn", "CheckedOut", or even "OutForRepairs", "Retired" etc.

In this case, all you need to do is change the status. You may want to capture who checked out the inventory, on what date, and what date the inventory was returned. You do need a second table to do, this but you do not need to copy / move the data.

Please read tje following links...
Fundamentals of Relational Database Design
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

 

[ApparentlyInept]

Jedel- I don't have a Table with the people who sign out equipment from me. Just the one with my equipment and the one that i'm wanting to input the sign out info into.

Willir- Yea I was able to get the combo box set up to where it would pull in the serial number for the item but I can't get the other two fields to automatically update with the corresponding information. I know I'm doing a horrid job of explaining all of this but communication was never one of my strong points. Sorry.

Ok, In TableA I have Serial Number, ITAMS Number, Nomenclature as fields. In TableB I have Serial Number, ITAMS Number, Nomenclature, Signed Out By, Sign Out Date as fields. I have a form created to TableB with those fields. Am able to set up the combo box to get the Serial Number Field to pull information from TableA to input it into TableB but I am unable to get the ITAMS Number and Nomenclature to automatically update with the information that corresponds with the serial number I am pulling from TableA. Maybe I'm overlooking something that's fairly simple but all the same its eluding me. Thx for your help folks I really appreciate it.

 

[jedel]

OK,

I'm on net with you now.

1. Create a form with the source of the table you want the data to go into, ie TableB.
2. On the this form place a combo box, use the wizard to select all of the data you want to place from the equipment from Table A. The tables' unique ID should be the first field. Followed by either the Srial number, description etc.

3. Place text boxes onto your form from the fields list so that the same data you have on your combo box will be the data source of the text boxes.

4. In the Combo box "After Update" event, set each of the text boxes the data from the combo box.The code will look something like this:

me.txtSerialNumber = me.combobox.column(1)

5. Just some quick points. The column count for the combo box starts at 0 therefore when you go ino the data SQL for the combobox to count along the fields, start your count at 0

6. You can show one field in the combo box by setting the column widths to zero except for the one you want to show.

7. Bind the combo box to one of the fields in the record.

If you have a finite amount of customers, you could do the same with the customers as well

Hope this helps


"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[willir]

ApparentlyInept

You only want to store the primary key from your first table on your second table. This one of the major advantages of using a relational database. Imagine you are renting out videos or DVDs. You store the video title in your invetory table and identify each video with a primary key VideoID. In your RentOut table, you record the rentals -- rent what, when to whom and when the video was returned. Now suppose you also stored the video title in the RentoOut table along with the VideoID. Then either because of typing mistake, or a law suite, the title for a video has to be changed. In this scenario, not only do you have to change the title in video inventory table, but you now have to change the title for records in the RentOut table where the video in question was rented out. This is called "maintenance". By only storing the VideoID field in the RentOut table, maintenance is reduced, and anytime you need to look at the details for the video, you use the VideoID to find the record in the Video inventory table.

...Moving on to your database.

TableA
SerialNumber - primary key (right??)
ITAMS
Nomenclature

TableB
OnLoadID - primary key
SerialNumber - foreign key to TableA
CustomerID - see discussion
CheckOutDate - date field
BeforeCondition - short text
CheckInDate - date field
AfterCondition - short text
Comments - memo field

Disucssion:
Only the serial number is needed in this table. There is no need to include ITAMS and Nomenclature fields.

Customer - normally, you would have a customer table. Why? Well, you will probably want the to know their phone number, address, etc -- makes business sense. And if your business booms, hopefully, you will have repeat customers.

CheckIn and Out dates -- obviously, dates the equipment was loaned out and returned. Any records with a CheckOutDate and no CheckInDate will be out on loan. Probably a good idea to document the condition of equipment before and after.

...The combo box
A combo box can be used to return more than one field or column.

SELECT Serial Number, ITAMS, Nomenclature from TableA

The combo box can display the serial number. And then you can use two unbound text boxes to display the ITAMS and Nomenclature values with the AfterUpdate event procedure for the combo box.

Open the form in Design mode and make sure the Properties window is open (from the menu, View -> Properties)

Select the combo box.

First thing to do is change the name of the combo box to a more meaningful name if necessary. Select the "Other" tab in the Properties window. The name may display something like Combo16. Change it to SerialNumber[/b]

Now select the "Data" tab in the Properties window and select the RowSource. Click on the "..." command button that appears to the right to open up the QueryBuilder.

Configure the query to display in order...
Serial Number, ITAMS, Nomenclature
Under the SerialNumber, sort Ascending.

Close the QueryBuilder and save your results.

BoundColumn: 1

Now click on the "Format" tab in the Properties window.
ColumnNumber: 3
ColumnWidths: .7";1";1" (Change as required -- these are the widths of the each column / field)
ListWidth: 2.7" (should match the total of ColumnWidths

Add two text boxes under the combo box, or wherever it makes sense. Select the each of the text boxes, and use the Property window to change the name of the controls to
txtITAMS
txtNomenclature

Now select the combo box again. Click on the "Event" tab in the Property window, click on the field for "AfterUpdate". Select "[Event Procedure]" from the drop down list box and click on the "..." command button to open up the VBA coding window. Type the following in blue...

Private Sub SerialNumber_AfterUpdate()
[COLOR=blue]
SetSerialNumber
[/color]
End Sub
[COLOR=blue]
Private Sub SetSerialNumber()

If Len(Nz(Me.SerialNumber, "")) Then
   Me.txtITAMS = Me.SerialNumber.Column(1)
   Me.Nomenclature  = Me.SerialNumber.Column(2)
Else
   Me.txtITAMS = ""
   Me.Nomenclature  = ""
End If

End Sub[/color]

Now go back to the form design window and select the form by clicking on the top left square box where the vertical and horzontal rulers meet. Click on the "Event" tab in the Properties window, select the Oncurrent field, and click on the "..." command button to go back to the VBA coding window. Type what is displayed in blue...

Private Sub form_Current()
[COLOR=blue]
SetSerialNumber
[/color]
End Sub

Save your form and your code and try the combo box box. This is what shoudl happen.

If the current record has a valid SerialNumber or if a valid SerialNumber is selected, Access runs the SQL SELECT statement which retrieved the three fields. The SetSerialNumber subroutine you created then displays the values in the two text boxes.

Note:
Spelling is real important here. If you spell the name of a control text box or combo box incorrectly, or the name of a command wrong, the code will not work.

...Moving on, improvements.
You may find it helps to change the name of your tables to more meaningful names. For example...
tblEquipment - for TableA
tblRental - for TableB

Avoid using spaces and special characters in the name of your tables, fields, etc. Otherwise, it makes it more awkward later own when creatnig forms or code.

Richard

 

[ApparentlyInept]

Thx guys your posts really helped me out.

 

[ApparentlyInept]

Maybe you can help me with this one too. A form along the same basic lines. Except on this one I'm wanting to basically delete the record from TableA (ITAMS) while I post the same three fields that I was working with before into TableB (Equipment Tracking). Or is there a way to simply move the record while only posting the desired fields?

 

[jedel]

Ok, This is where I recommend you have an autonumber in every table you create and make that number your primary key. Then all you have to do is write an SQL that deletes all records in the table with the ID number of the field in your form. There will only be one of them if its a unique number. Here's how

Create the form you want based on the table you want to delete the records from. I recomend a continuous form with a combo box in the footer that allows you to search for a record, but hey that's just me. This will work on pretty much any layout.

Place a button on the form and cancel the wizard.
Open the query buyilder and create an append query that places the three records back into TableB. Once you have done this view the SQL and copy it.

Dim defin as string
defin = **********"Paste Append Code from Query Wizard here"****
Docmd.RunSQL defin

defin = "DELETE * FROM TABLEA (((Where TABLEA.ID)= FormB.ID))"
Docmd.RunSQL defin

Open the Query builder and start a new query. Once you have a start, change the query to a "Delete" query (same method as an append).

Once you have created the query switch the view to SQL view copy the text and paste it into the Buttons "on Click" event. The code will look like this:


The code may look slightly different, but you get the idea.

POINTS:
You will get a warning message when you delete records. You can if you wish turn this off, but you MUST turn it back on again at the end of the code or when you come across other errors in you DB, you will not know. Here's how

Just before you define the defin tag place this line

Docmd.SetWarnings False

at the next line after the Run SQL set the warning back to True:

Docmd.SetWarnings True

And there you have it.

Hope this helps.



"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[ApparentlyInept]

) Guess its a good thing that in TableA my pk doesn't allow duplicates seeing as its serial numbers and the last thing I want to do is track multiple events of the same equipment. Thx Jedel, gonna see if this works.

 

[ApparentlyInept]

That will work for one thing I'm wanting to do, so thx Jedel but how would I delete a record from TableA while pulling all the information out of TableB. Because what I'm working on now is I have one form that posts the information into TableB when I ship the item out. What I'm wanting is to be able to delete the record out of TableA when I recieve the delivery confirmation and go into the form I have set up to input that date. Would this be a OnClick operation? Right now I can put in all of my information for TableB and have that show that the item was delivered. I was just hoping there would be a way to automate the deletion of the item from my main table without actually having to go in and physically delete it. The post you gave me may work but if it does I must just be lookin at it wrong.

 

[jedel]

AS long as you can identify the unique ID from your table on the form, you can delete any record from any table in your database from that form. You simply create a delete query using the wizard with the criteria set to reference the unique ID. Copy the SQL code and paste it into the button's "On Click" event.

I have a DB at work that does the same sort of thing. It Appends records based on a unique ID to another database first, then it deletes the records from the original source.

I guess the key here is that you can ,ake the append query copy records over to anu table, you don't need to append every field. Do this first and then run the Delete query SQL afterwards using the unique ID. If you haven't got the unique Id for the record, you could look it up with a combo box as we discussed earlier.

If you are still have problems, post the code and i'll have a look at it

Cheers

Dean

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."