!!Please Please Help!! Inserting my Data

[dinorbaccess]

Hello everyone,

please I have an urgent Project to develop. Can someone teach me how can I insert data in a related table with append query or another way. So here what I have:

tblCostumer :{ID, CostumerID, CostumerName}

tblCOrders:{ID, Date, OrderNote, Preis, CostumerID}

These tables are linked with CostumerID, that means each Costumer as its own folder with OrderNotes, Dates and Price.

What I need to do is that each time when I create a new costumer I want to be automatically inserted records in field Datum of tblCOrders from Date 01.01.2011 until 31.12.2011 that means 365 days. So than I can just put the notes directlly to the related date.
I have also a field named Prise. I want that in this field to be Inserted the value that I give in the textBox Preis which I have placed in my form.

Below you have my attachment

Is that possible?

I would be extremly thankfull if someone could help.

[RuralGuy]

Why on earth do you want to create 365 record needlessly? Why not just create the records you need?

[pkstormy]

I agree with Allan but if you need to do this, you'll need to write a bit of vba code (I have to do the same type of thing for our anti-tobacco program to setup the table for entering dates a patient smokes and it must be written beforehand.) Here's an example (hopefully you're familiar with vba coding since it's difficult to do via a query):

The following is a function you would call to write the dates to the table (this is ADO type code). (Note: CustomerID, StartDate and EndDate are passed to the function)

Function WriteDateRecords(CustID as variant, SDate as Date, EDate as Date)
if isnull(CustID) then
msgbox "ERROR: CustomerID was not passed to the function!"
exit function
end if
dim rs as adodb.recordset
set rs = new adodb.recordset
dim XDate as Date
dim strSQL as string
strSQL = "Select * from MyTableName"
rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
For XDate = SDate to EDate
rs.Addnew
rs!CustomerID = CustID
rs!MyDateField = XDate
rs.update
Next XDate
rs.close
set rs = nothing
End Function

You would then call the function such as:

Call WriteDateRecords(me!CustomerID, #01/01/2011#,#12/31/2011#)

But ideally you would want to pass date values based upon either today's date (and plus 365 days) or date fields on the form.

Ex:
Call WriteDateRecords(me!CustomerID,Date(),Date()+365)

[pkstormy]

I don't normally do this (especially for new members that signed up yesterday with only 1 post) but here is the code in the mdb. You'll need to learn and understand the code to make any modifications (it's fairly basic code that opens a recordset and does a for-next loop to cycle through the dates). Again, it's ADO code versus DAO.

The function to write the dates is in the module called: Write Date Records and the function is called: WriteDateRecords. It is called in the OnClick event of the 'Write Dates' button on the form.

I would recommend you rename your tblCostumer form to frmCustomer since tblCostumer is not really a good name for a form. I might also change the ID field in the table to CustomerID so that it's easier to understand when linking the 2 tables together (I personally like to keep fields that are linked between 2 tables the same name.)

Again, you'll need to learn a bit about how to write vba coding to make any modifications. Hopefully this will get you started on learning vba, functions, and how to open recordsets in code. Note that there are also other ways to do this but difficult to do with a query. VBA code is ideal for this type of thing.

If you're in a developers position, this will be a great time to start learning how to do things like this since you'll most likely need it again for further development. I hope this helps get you started.

(Note also: the call to the WriteDateRecords function currently passes today's date to today's date+365. If you want to make it start on 1/1/2011 to 12/31/2011, you'll need to change the OnClick code of the button to call the function to: Call WriteDateRecords(me.ID.value,#01/01/2011#,#12/31/2011#) Ideally you'd want to have the start date and end date on the form (as unbound fields with a default value of 1/1/2011 for the start date and 12/31/2011 for the end date or defaulted to the current year's start date and end date) and then call the function like: Call WriteDateRecords(me.ID.value, me.StartDate.value, me.EndDate.value). That way if need be, the user can enter any dates they want to or if 2012 rolls around, you can easily change the default values of these fields versus modifying the code.

[dinorbaccess]

@pkstormy

Man, how can I thank you!!! You really saved my life. You have no Ideea how I was strugling to find this out. And It true that Im a new member here, because I found this forum just yesterday. Im not that beginner with Access. I have stardet a month ago writing VBA codes in Access. But Im still not very good at it.

But I can say you are very very good. Can you recomend me where did you learn to program those complex modules.

Thank you again man. I promise, I will do a lot for this forum.

[dinorbaccess]

@pkstormy

I understand now a lot of code that you have written for me. I wantet do do that with querys (append querys) but I couldnt figure it out. Your way is a lot more easier. Thnx again.

I will study more youre code that you written me, but maybe you can help me more on this.

I placed a field Price in my form. How can I take the value that I typed in this textxbox to my table tblOrders.
I think it will be easy to do this, and maybe I can find it. But Is it a lot of work needed, if I want that from a specific Date (e.g 01.02.2011)the value that I type for field Price to be the default value for all bottom fields.

So for example. With your code I will add records for 1 month (e.g. January) and for this month i want to be insertet the value of my textbox Price in every record of field Price.
But, if I need to add then records individually for Price, I want that this field to take the default value always the last value that I give in this field.

Thnx thnx againg. You have really motivated me. Ive make a big project with ms access. Ive programmed a lot of thinks in my project, but these thing are really challanging me.

[pkstormy]

If I read you correctly, you want whatever price is last entered to then be the default for new records.

To do this, you manipulate the DefaultValue property of the Price field.

For example:
Private sub Price_AfterUpdate()
me.Price.DefaultValue = "=" & me.Price.value
or
me.Price.DefaultValue = dlookup("[LastOfPrice]","MyQuerytoShowLastPriceValue")
end sub

This would then set the default value of the price to whatever value is last entered (until the form is closed).

You could also utilize the Dlookup command to find the last price entered and again, set it as the default value (this is probably the better way).
ex (for the defaultvalue property of Price):
=dlookup("[PriceField]","myQueryNameThatShowsLASTPrice")

The query then would be a query which shows the 'last' Price value (ie. you'd use the summarization button and then change the autonumber field to be the Last (or Price to be the Last).

Depending on how you do it, you may have a dlookup such as:
=dlookup("[LastOfPriceField]","myQueryNameThatShowsLASTPrice")

[pkstormy]

Here's an example that will populate the Preis field with the last price (see the function in the module and the query). (you can also take the =Dlookup(.... in the function and put it in as the defaultvalue for the Preis field on the subform.)

[pkstormy]

Can you recomend me where did you learn to program those complex modules.

I used "MSAccess Developers Guide" by Sybex/Ken Getz book. A great book.

[dinorbaccess]

I used "MSAccess Developers Guide" by Sybex/Ken Getz book. A great book.

Thank you man.

And now my problem (Thread) is solved. Thnx to you.

Your great.....