CODEWITHSUNDEEP
ms-accessms-access-2007vba
Hitik
Hitik

Reputation: 21

Set up Access table to perform multiple records at time

I performed some research how do I need to set up my DB but I need your advice how to.

I have few tables in my db ( db is for incoming material ) in this db are below tables:

  1. Material table
  2. incoming delivery
  3. measurements
  4. supplier
  5. time measurement

Let me explain logic of this db.

When delivery come user will input some data in form (creation of incoming list) where he will basically enter all data necessary to start process of receiving. So once he hit button save record he will create record in tables incoming delivery and time measure.

Until this point everything works perfectly. When next user received this incoming list he got some data where was one hyperlink to file where they put it measurements.

And here come my problem. I want data to be input in Access rather than to excel (form input looks much more better [yes this is most important reason :) ] ). So for that I created table called measurements, where I plan input [incoming delivery ID], [material id], [primal key] , and that 41 another columns for measurement(this columns need to be separated cause we have many parts and each got different No. of measurement and user will get information via user form ( opening different form based on material id [this works]).

So after describing its logic I am requesting you people how do i create with 1 record to measurement table each time different numbers of measurements in measurements table for it.

put it even more simple just for case. When user hit button to save the record which creates record in delivery list will also create for example additional 5 records (this number will be based on cell value) in measurement table linked with incoming delivery. (relation is of course set up to one-many)

so in the end when i will create somehow continuous table for data input. User will see form where he got incoming delivery No. some information from other tables and as mentioned 41 items to measure 5 times ( 41 columns and 5 rows )

Hope that my explanation is clear and rly need your help i am screwed :D

Upvotes: 0

Views: 1105

Answers (1)

Barranka
Barranka

Reputation: 21047

Hints:

  1. Use VBA to automate the creation of records. Look for information about DAO and/or ADO and how to use them to insert records (I personally use DAO when I work with Access, it works but it's old).
  2. Do your homework. Before asking a question, it is important that you do your research and that you try to solve the problems by yourself. Try to help yourself before asking others. Please read this article.

Maybe this snippet of code can help you. You'll need to call this method from an event (button_clic or something in your form):

public sub addRecords(id as integer)
    dim db as dao.database, rsIn as dao.recordset, rsOut as dao.recordset
    dim strSQL as String
    dim someValue as integer, i as integer ' Test values
    ' "Connect" to your current database
    set db = currentdb
    ' Create a recordset with the input data you need (read only)
    strSQL = "select * from tbl_inputTable where id=" & id
    set rsIn = db.openrecordset(strSQL, dbOpenDynaset, dbReadOnly)
    ' Create a recordset to your output table
    set rsOut = db.openRecorset("tbl_outputTable", dbOpenDynaset, dbAppendOnly)
    ' Read the data from the input table
    with rsIn
        .moveFirst
        someValue = rsIn![aField]
    end with
    ' Write some test data to your output table
    with rsOut
        for i = 1 to someValue
            .addNew
                rsOut![fk_id] = id
                rsOut![theValue] = i
            .update
        next i
    end with
    ' Close every recordset and databases (this does not close your application)
    rsIn.close
    rsOut.close
    db.close
end sub

In your input form, write this in the "On Click" event:

sub button1_click()
    call addRecords(txtId.value) ' I am assuming that there's a text box called "txtId"
end sub

This is just a sample of what you can do with DAO. I won't (and maybe nobody else would) write the full code for you: You'll need to fit this to your particular problem.

Upvotes: 1

Related Questions