Manipulating table data from code

[derekinstoke]

I’m working on an Access 2002 project where I need some help with coding. I’m a neebie to Access and Visual basic so a few pointers to get me on my way would be much appreciated.

The project is dealing with logging vehicles on and off site. I have one database that contains a list of all regular traffic in three fields – Registration Number, Company Name and Type. After the last update this contained about 300 records. I called this MAINDATABASE.mdb.

I have another database that runs as a user input screen and front end called WORK.mdb. This contains the above three fields and three others, Time In, Number of Occupants and Time Out.

I have already done the coding for Time In and Time Out where the user just double clicks the field and the system time is entered. The important field is Registration Number. What I need is the following:

When a vehicle comes on site the user double-clicks Time In on the form which inserts the present time (done). The user then tabs to or clicks on Registration Number. An input box comes up where the user types the reg number. I need the code to take this input, search the MAINDATABASE database and if the reg number is found return the values for Registration Number,Company Name and Type in that row of the database and then insert those values into the corresponding rows in the WORK database. If the reg number isn’t found then a message box comes up telling the user to enter details manually and input the string entered by the user into Registration Number field on the WORK database and leave the user to enter the values for Company Name and Type. I have the code for the Input Box and the Message Box at the end!:


Private Sub REGISTRATION_GotFocus()

InputBox("Enter Reg number...")
……..

MsgBox "Registration number not found. Please enter details manually"

End Sub


I just need the stuff to do the work in between!!
Any pointers are very welcome……

 

[Ian Mac]

Hello,

Welcome to the board.

Firstly, a point about how you are recording your information.
You shouldn't be recording Registration Number, Company Name and Type in your WORK Database.
You need to assign a Unique Reference in your Main Table.
You can add a field (Call this VehicleID) and make it an Autonumber, this will be your Primary Key (Rightclick the field name in design mode and select Primary Key) in the Main Table in MAINDATABASE.

You can then create a table in your WORK db with the following Fields/DataType.

LoggingID - Autonumber (Primary Key)
VehicleID - Number/Long (Foreign Key) <---- this will be linked to your Main Table.
TimeIN - Time/Date
TimeOut - Time/Date

Now you need to set up the relationships.
You need to link the 2 VehicleID's together - Check the box Enforce Referential Integrity.

Now what you can do is run a Query which will pull the data together for visual purposes.

You will need more to go on but do the above first before continuing and post back any questions.

 

[derekinstoke]

Reply to Ian

I think I should explain further. I know I said Maindatabase was a separate database (it does exist in it's own right as well) but I have imported the table containing the details - Registration Number, Company Name and Type into my Work.mdb database already. The maindatabase table has a primary key (ID -autonumber) so each individual vehicle has a unique ID.I have already linked the two tables.

I don't want to view the three details, I need to copy them into the active form/table in Work.mdb so I don't think a Query will work here? I need the code to extract the text from the fields containing Company name and Type in the maindatabase table and transfer it into the corresponding fields (with the same names) in the Work.mdb table. The problem is here that I don't know how access actually identifies individual rows and columns. I've got the "Dummies" book but that doesn't go anywhere near what I want.

The idea is that Access takes the String inputted by the user in the "Enter registration Number" InputBox,searches for it in the maindatabase table then copies the text from "Company name" and "Type" in the maindatabase table into the associated fields in the Work.mdb table, along with the reg no as well.I'm sure it's something like Rows.Item to identify the fields but I have no idea how to actually get there!

Any thoughts

 

[Ian Mac]

Hello,

I think you're are missing the point of what I'm saying.
I'm in a hurry currently but will post an example DB tomorrow.

Can I ask First what the Registration Number, Company Name and Type are in your main table.

I understand Reg, but Company (is this make of vehicle or something else?)
and Type???

Just for clarification.

I'll post the DB tomorrow.

Cheers,

 

[derekinstoke]

More explanation...

It's difficult to explain exactly what you want to do on a forum. Hopefully you get the general idea and I appreciate the help. In answer to your question : Registration Number is the registration number or index number(U.S?) of each vehicle i.e what's on the number plate, Company name is the name of the firm that owns the vehicle and Type(I really should have explained this better!)...Type is a selection from a drop down list(done). The user can choose Supplier, Contractor, Site services etc.

I've put a rough copy of the database onto my online storage site. If you want the address to download it let me know. It might help to see what I've got so far?

If you get some time to think about this I'd appreciate it.

Thanks