Solved

vba insert into query with autonumber foreign key   help

Posted on 2004-03-31
15
955 Views
Last Modified: 2012-05-05
Hello;

I have a tbl_bookings table which has a Invoice_Number foreign key which is linked to the Invoice _Number field primary key in a table called tbl_invoice. At present the insert into vba code is as follows, which will not work as i have not dealt with the autonumber field. I have tried using the Invoice_Number filed in the tbl_bookings table but it doesnt work as it needs to be update from the table tbl_invoice. The query so far is as follows without the Invoice_number


CurrentDb.Execute ("INSERT INTO tbl_Booking (Booking_Start, Booking_End, Room_Number) VALUES (#" & Me.Booking_Start & "#,#" & Me.Booking_End & "#,'" & set_room & "');")


Any suggestions
Cyart
0
Comment
Question by:Cyart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 125 total points
ID: 10727470
Remove theinvoice number from the booking table and and create intermediate tblInvoiceBooking.
Thus All combinations of Invoice and Booking will be possible:
One invoice for multiple bookings
One booking having multiple invoices when two persons want to split the bill....

Nic;o)
0
 

Author Comment

by:Cyart
ID: 10727544
Many thanx for all your help much appreciated

Paul
0
 

Author Comment

by:Cyart
ID: 10731818
Nic;0)

Tried what you suggested, I created another table tblInvoiceBooking. I took the Invoice_Number field foreign key from the tbl_booking table. I then placed the booking_Id and Invoice_number foreign keys in the new table tblBookingInvoice. Cant get it to work is this what you ment as when I create a query for entering invoice and booking information as soon as I am in the sub form entering booking information I get an error. When I then progress to the next invoice and then go back to the one I have just created all the booking information is lost.

Paul
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:nico5038
ID: 10732130
For the filling of the relation table you can use it in a linked subform.
Just add a subform on the invoice to fill the booking key or
place a subform on the booking and fill the invoicenumber.

Make sure the link is filled, when placing the subform access will ropose a key and normally that will be the correct one.

Nic;o)
0
 

Author Comment

by:Cyart
ID: 10732333
Nico;),

Dont mean to ignorant I have always created a query and populated it with all required fields and then used the form wizard and based the form on the query. I have never created a form and then included a linked sub form how is this done?

Paul
0
 

Author Comment

by:Cyart
ID: 10732534
Nic;o)

I am really having problems I do not think I have got things right at all, please can I send the db to you so you can tell me if the tables and relationships are correct so you can tell me what is going wrong. I created a linked form however it cant be correct as it is still loosing information, if you have time could you show me how it is done. Sorry to be such a pain!!!!

Paul
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10733168
No problem, you know the way :-)

Nic;o)
0
 

Author Comment

by:Cyart
ID: 10734589
did you get the file ok
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10735258
Yep, will however have to wait till tomorrow I'm afraid...

Nic;o)
0
 

Author Comment

by:Cyart
ID: 10735398
no probs thanx for help
0
 

Author Comment

by:Cyart
ID: 10739742
Any Joy with the db? Would an sql query using an inner join within the code for the Book button on the calendar form get around the problem?

Paul
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10740859
Hmm, I don't think so.
I would start with programming the setting of the bookings that are active in the month for the rooms.
Thus it's visible which rooms are already booked.
Next I would expand the OnClick on the dates to detect or the backcolor is set to vbRed and then place the bookingID in a hidden text field you can use to link a subform to that displays the booking data belonging to that date.
When the room/date is white then use my start/enddate coding also expanded to check for one room.

The booking button can then be implemented with the INSERT as described.

Clear ?

Nic;o)
0
 

Author Comment

by:Cyart
ID: 10741080
Nic;o),

Thanks for the reply, Not sure what you mean!,

"<I would start with programming the setting of the bookings that are active in the month for the rooms.
Thus it's visible which rooms are already booked.>"

I have sent you the correct copy of the database if you can view April 2004 I believe I have already sorted out the bookings that are active and when a date is clicked and is all for the correct room the backcolor will change to green then click the book button the start and end date text boxes will get populated.


Not sure what you mean by the following statement.

"<Next I would expand the OnClick on the dates to detect or the backcolor is set to vbRed and then place the bookingID in a hidden text field you can use to link a subform to that displays the booking data belonging to that date.
When the room/date is white then use my start/enddate coding also expanded to check for one room.>"


Cant get this working because of the foriegn autonumber Invoice_Number key in the booking table

"<The booking button can then be implemented with the INSERT as described.>"

Can you  show me what you mean in the database I sent you.

Cheers

Paul


0
 

Author Comment

by:Cyart
ID: 10742737
Nic;o)

Can you see any problems with my db reference the above?

Paul
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10742900
Hmm, I see you still didn't remove the invoice number from the tblBooking and created a relation table.
Do that first, thus the link can be done afterwards.
You'll always have the possibility to place a unique index on either the invoiceID or the bookingID to limit only one of them to be linked to another.

Nic;o)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question