Solved

vba insert into query with autonumber foreign key   help

Posted on 2004-03-31
15
956 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 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