Solved

vba insert into query with autonumber foreign key   help

Posted on 2004-03-31
15
949 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now