Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vba insert into query with autonumber foreign key   help

Posted on 2004-03-31
15
Medium Priority
?
961 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 500 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

715 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