Create a Form to Update a Table . . .

[OscarAGC5]

Howdy All! New to the forum and pretty new to Access (I'm better with Excel).

New job uses a lot of Access - I need help desperately :banghead:

I've got two tables - one that works like a cypher and one with all my records.

I have 2 queries. One that is a simple query that extracts data that is not so user-friendly and puts it into a user-friendly format. The Other query is for any records that the first query excludes because the new combination of data (lets just call it a code) is not in the cypher table, but needs to be added.

How do a create a form that will show the records in the 'excluded records' query and allow me to select from a drop-down list a specific set of categories to update the cypher table ?

Example :

Record Table: Cypher Table:

Ford Focus = Sedan
Ford F-150 = Truck
Ford Freestyle = Wagon
Ford Escape = SUV

Now a new model comes in, a Ford Edge which is an SUV but is not in my cypher table.

I have the query to pull in the excluded Ford Edge, but I want a form to show "Ford Edge" in the first column and be able to select from a drop-down list "SUV", and either automatically update my cypher table or require me to press a button to update the table (whichever is easier).

I don't know VBA or any programming languages (I need to learn, I know)

But I know this shouldn't be difficult to achieve.

CAN ANYONE HELP PLEASE!?!?!?!?!

 

[Ranman256]

Normally with access you don't have to know VBA. It is built to do almost everything with queries and macros.

Now, is it possible to key your cypher table? That way, you can add Ford Edge since it isn't there, and the keys will prevent you from adding existing cars.

If not, you can make an OUTER join on the two tables. Bring down car from both tables, but put NULL under the criteria for cypher.car.
This will show you what's NOT there.

 

[OscarAGC5]

Yes - it's definitely possible to modify the cypher table manually, but the whole point is to have a form that updates the cypher table based on the excluded record query. The idea is to create a control process that isn't just jumping between tables to copy and paste data.

 

[jdraw]

Just curious -- How/why is this table named Cypher?

Is it referring to Model or bodytype or vehicle type?
What happens if you try to process Chev Orlando, or Toyota Camry, or KIA Optima?

 

[OscarAGC5]

How/Why = I need to identify the new vehicle with a generic type, because the code that I'm using to identify the vehicle (just for example) is like this :

Record Table: Cypher Table

DOMESTIC - Ford - Fusion - V6 - 2014 = Sedan
IMPORT - KIA - Optima - I4 Turbo - 2015 = Sedan

If the Kia was not in the Cypher table, it would show up in the 'excluded records' query because I'm using the cypher table in my 1st query to show whether the vehicle code equates to a sedan, truck or SUV. The record gets excluded because the record table code, i.e. IMPORT - KIA - Optima - I4 Turbo - 2015 , is not in the cypher table already, hence needing the updated.

The form to update the table is to avoid have to go into the records one by one to pull out the code to determine the cypher category and then add it to the cypher table.

The update form would just show :


Record Table_Code: Select Category from Dropdown:

IMPORT - KIA - Optima - I4 Turbo - 2015 Truck
SUV
Sedan (Click this and update cypher table)

 

[jdraw]

I don't know the purpose of your database, but you might consider additional tables. If what you have satisfies your needs, great. But if you need to find more detailed info, you might consider extending your data model.

eg (any or none may apply to your needs)
VehicleType (generic - truck, sedan, coupe,SUV, van...)
Manufacturer( Ford, GM, Chrysler, Volvo, KIA, Honda, Hyundai....)
VehicleModel ( Fusion, Edge, Focus, Malibu, Optima, Rio, Sorento....)
Engine ( I4, I6, V6, V8....)
VehicleDetails( YearofManufacture, doors, seats.... whatever makes sense to you)

You may get ideas from this generic model.

 

[OscarAGC5]

The cars is just for example. The reality is I could have something come up that's entirely new that I would have no advance knowledge of. When the 5 criteria come together, only then can I determine which category the product belongs to. The five criteria I'm combining may not be consistent which is why I need to see it in order to update it.