Hi, Please help me on this project.:banghead:
I have a relational database with primary key in each table and foreign key in child tables. My goal is to have updated inv_ID (foreign key) column in table: tbl_allPins every time a new invoice is added to tbl_invoices and a primary key is generated as inv_ID in it.
Consider I am new in access with a very limited knowledge about VBA. If you keep it simple while explaining, I will appreciate it. Please look at the attachment.
Hi John,
I created a form that inserts data in tbl_invoices. A sub form created inside the frm_invoice. I added an update query underlying a SAVE button in subform and try to update the tbl_allPins with inv_ID but it is not working at all. The save button is there when you open the form in "Design View" and disappears when you open it in "Form View", nothing is working. Please tell me how a subform can help to retrieve a newly generated autonumber (primary key of the last record in parent table) from a table and insert it into another table (where it is FK)
Hello John.
Here is a copy of my DB attached. It is not completed yet but you can see the relationships and get the concept. Thanks for all your helps.
The method to add an ID from a parent table (PK) to a child table (FK) simultaneously using a single form is very helpful to get this project done (I believe so!). View attachment DB4Test.zip
Please confirm when you get the attachment.
Thanks
This is suppose to hold all data's about the sold calling cards, invoices, retail stores who sell my cards and customer comments when they call for any reason, bad quality, bad connection and etc.
Another table including all call duration report (CDR) will be added to this database in future.
I am going to enter all records from a bunch of paper invoices that I have on my desk into the tbl_invoices using the form.
All records from my customers that have called me during last year with any comment is written on the paper and I want to add them into comments table.
I am the only person who uses this database for now (single user).
For instance in my first invoice I have sold 50 cards between range 5000 and 5049 to one of the vendors on Jan 1, 2012 and he's given $200.00 in cash to me. When I enter the data from the first invoice in the form and click on the SAVE button it must save all records in related fields in the first row of invoices table (inv_ID: autonumber=1) and add the inv_ID (here is 1) in front of all serial numbers from the beginning to the end of the range (between 5000-5049 in "tbl_allPins"."inv_ID").
Almost the same concept can be used for comments table. So if you help me to solve this problem, I will use the same strategy for the other one.
I am not sure if it is clear enough or not.
Thanks for your time.
I'll have to think about this. I may not get a chance to really have a good look at it until later on this week, possibly not until the coming weekend. So if anyone else feels like jumping in, in my absence please do.
John, I'm not able to fill your shoes but I will give it a shot...
Hi silversun,
I tried to get into your problem. I had a look at the attached database and, if I understand you right, there are pins to be assigned to an invoice right after it was inserted. You determine these pins by the range given with the two values 'serial_from' and 'serial_to', right? - If so, you'll find the result of my work attached.
In order to achieve this goal (and improve your application a bit), I took the liberty of changing a lot:
1. I deleted the field 'str_name' in 'tbl_invoices'. The field 'vendor_ID' is sufficient. Otherwise you would have to face the danger of redundant data. One of the main principles in database design is: every information should be contained only once.
2. I changed the configuration of 'vendorCombo': the bound column has to be 'vendor_ID' and not 'str_name'.
3. I changed the control source property of the textbox 'vendorID' from 'vendor_ID' to ' =[vendorCombo].[column](0)'. In a certain way this was another case of redundance because 'vendorCombo' should be the one and only control bound to this field.
4. I made the textbox 'inv_ID' visible, but only for proof of concept. And I renamed it - like some other textboxes - to 'txtInvID' in order to avoid trouble with expressions used in the query 'qupdPinsWithInvID' which I created. You can see the use of this query having a look at the code module.
Mostly I changed your subform. The reason why you can't see your command button is it's view mode. In 'table view' you can't see any additional controls except the textboxes and their related labels. Therefore I transferred the button to the parent form. Currently there are only 3 columns shown in the subform. You may add more of them - or change to others.
After running some tests there remain two records in 'tbl_invoices'. I did this on purpose.
Finally I hope my explanations are simple enough for you.
Don't get frightened by reading some VBA code. I put comments whereever possible to help you understand what's going on. Take your time and try to get through it day by day and you will see the advantages soon.
Hi starGrabber,
Thank you for all you did on my project. I could not guess how hard it is to add an ID into a table.
Is there anyway to execute qupdPinsWithInvID by pressing the same key where saving the invoice details? (using a kind of macro)
I have some more questions about your code. Please let me know if I am allowed to ask.
Thanks
of course you can execute 'qupdPinsWithInvID' automatically after saving the invoice details. But: "by pressing the same key"? I guess you want to say 'button'. I missed this 'Save' button in your form, so we have to click either on the record selector or navigate to the next (e.g. new) or previous record with the navigation buttons.
Don't take this wrong but a record selector is a good thing in detail forms - for beginners! - So get rid of it one day and put a 'Save' button in the form. - But in table view forms it's ok.
You can execute the query by the 'Form_AfterInsert' event (before you change anything in your application make a backup!):
Code:
Private Sub Form_AfterInsert()
On Error GoTo Err_AfterInsert
' If there are no values to process, exit the procedure:
If IsNull(txtSerialFrom.Value) Or IsNull(txtSerialTo.Value) Then
Exit Sub
End If
' Declare a query definition object and reference the query "qupdPinsWithInvID":
Dim qry As DAO.QueryDef
Set qry = db.QueryDefs("qupdPinsWithInvID")
' The query needs 3 values. These have to be set from left to right:
' First, the value which has to be updated:
qry.Parameters(0).Value = txtInvID.Value
' Second the value of 'txtSerialFrom':
qry.Parameters(1).Value = txtSerialFrom.Value
' And third the value of 'txtSerialTo':
qry.Parameters(2).Value = txtSerialTo.Value
' Run the update query:
qry.Execute
' Selfexplaining (I hope...)
If qry.RecordsAffected = 0 Then
MsgBox "There where no records to be assigned.", vbExclamation
End If
' Let's see the result (refresh the subform):
Me.tbl_allPins_subform.Requery
Exit_AfterInsert:
Exit Sub
Err_AfterInsert:
MsgBox Err.Description
Resume Exit_AfterInsert
End Sub
If you want to do it this way, delete the procedure 'cmdAssignPins_Click', rename the button 'cmdAssignPins' to 'cmdSave' in your form and create a new procedure:
Code:
Private Sub cmdSave_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub
Delete also the procedures 'Form_AfterUpdate' and 'Form_Dirty' as they are not needed any longer.
The 'Form_AfterInsert' event is fired every time a new record is saved.
The reason why I put a separate button to run the pin assignement is that probably something goes wrong adding a invoice. Imagine there is a control value missing or false. As you don't have any input validation procedure it is safer this way. But... it's your decision.
As you surely noticed by now, I don't use macros. The reason is the same as with the record selector. - Microsoft tells us somewhat about security issues using VBA code (see Access online help). I think you should consider them only when developing an Access web application.
If you are familiar with macros, go ahead (I'm hardly!). But sooner or later you will experience their limitations and downsides. The only two macros that are useful are the AutoExec and the AutoKeys macro.
A further test of 'invoicesHere' showed the need of changing the 'Form_Current' procedure like this:
Code:
Private Sub Form_Current()
If Not IsNull(txtInvID.Value) Then
' When you navigate through records, update the vendor data:
Call SetVendorData
Else
' otherwise clear the related controls:
address.Value = Null
city.Value = Null
zip.Value = Null
commission.Value = Null
End If
End Sub
And I forgot one little but important detail: a ORDER BY condition at the end of the SQL statement of your subform recordsource property. Add it as follows:
... WHERE tbl_allPins.inv_ID=[Forms]![invoicesHere]![txtInvID] ORDER BY tbl_allPins.serial;
Feel free to ask whatever you want, anytime (this is the intention of a forum). And if I don't have the time or the knowledge, surely others will jump in.
Hi StarGrabber,
Here are my questions. I will appreciate all your helps.
In line 10 procedure cmdAssignPins_Click() checks if there is no value to process, exits the procedure. Since the button is not active at this point why do we need this check?
In line 39 what is the purpose of "Exit_Assign"?
Why do we need to keep procedure: txtSerialTo_AfterUpdate() where you have covered all parameters in procedure: SetVendorData().
In line 70 you have enabled cmdAssignPins button. How it switches back to disabled. I could not get it.
In line 91 is it acting as a connection to a database? If yes, why we do not execute it at the very first command.
In line 98 is it acting like a disconnection from a database? If yes, why it is not at the end after all procedures?
In line 131 I added: fee.Value = 5 - (5 * ((rst(3).Value) / 100)) to calculate the value of text box "fee". It was missed.
My understanding is the order of procedures does not matter, am I right?
If you look at the frm_comment and see the related query you will see how it works. Only one query executes in a macro. I was hoping to find a similar solution. I tried a lot and there was no success because of lack of knowledge. Do you think we can find a solution like that?
If I want to do another similar task with other tables in future, do I need to write all these codes for that table as well? If yes, please explain about the order of the procedures.
1. The button 'cmdAssignPins' is not only 'not active' but also not existent (if you renamed it to 'cmdSave'). I told you to delete the procedure 'cmdAssignPins_Click' (and others).
2. 'Exit_Assign' is a jump mark which is mostly needed when there is an error handling section in the routine. In the second line of it you can see 'On Error GoTo Err_Assign'. If an error occurs, VBA jumps to the code line assigned by the mark 'Err_Assign', displays a message and then jumps to 'Exit_Assign'. In VBA you can name jump marks after your fancy but, like everything else, it should be descriptive. Using a jump mark to exit a procedure is convenient when there are variables to be reset after an error handling and/or other actions the developer wants to run at the very end. I put this marks in nearly every routine to make you familiar with this schema (do you have good (e)books or other sources next to your pc?? And don't forget to use the online help).
3. The question, why we should keep the procedure 'txtSerialTo_AfterUpdate', you have to ask the developer of your application. - I think its purpose is to fill the two textboxes 'invAmount' and 'fee'.
4. The button 'cmdAssignPins' switched back to disabled every time the 'Form_Dirty' event is fired. This happens as soon as you change the value of a bound control. Have a look to the routine 'Private Sub Form_Dirty(Cancel As Integer)' again. The opposite happens in the routine 'Form_AfterUpdate()'.
5. Lines 91 and 98? In my module line 91 shows 'Private Sub Form_Unload(Cancel As Integer)' and # 98 'Private Sub SetVendorData()' but I think you mean the commands 'Set db = CurrentDb()' and 'Set db = Nothing'. If we use the so called 'DAO data access interface' we first need a variable (here 'db') to reference the database we want to work with. As we need 'db' in more than one routine, I declared it one time for the scope of the whole module (see line # 4), instead of inside the routines.
6. Yes, you're absolutely right: the order of procedures does not matter. There are only two possibilities: either there is an event which was fired or you call a routine explicitly (e.g. 'Call SetVendorData'. The word 'call' is optional but makes the code easier to understand).
7. Sure we can find a "similar solution" with 'frm_comments'. If I understand the purpose of the field (or textbox) 'PIN' right, you want to insert a comment for only one PIN. But on the other hand you made a relationship between 'tbl_comments' and 'tbl_allPins' that makes believe one comment has to serve for several PINs. This doesn't feel right. In that case you have to make it possible to assign the comment ID to a range of pins, similar like we did it with the invoice ID. - But for the first case you can do the following:
- add a new textbox ('txtCommentID') to the form
- set the control source property to 'comment_ID'
- insert a few lines of code:
Code:
Private Sub btnSave_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub
Private Sub Form_AfterInsert()
DoCmd.RunSQL ("UPDATE tbl_allPins SET tbl_allPins.comment_ID = " & txtCommentID.Value & _
" WHERE (((tbl_allPins.pin)= " & txtPIN.Value & "));")
End Sub
Please note I've changed the textbox names and the name of the 'save' button. If you want it to work, you have to adapt these names in your form. Set the property 'On click' (where you had the embedded macro) to '[Event Procedure]'. Maybe you are astonished not to see expressions like 'DAO.QueryDef'. Well, there are other possibilities too and this time I chose one that gives you a less hard time (I hope).
You see only the essential code here, without error handling and validation.
You don't need to write "all these codes" for other tables as well, but you can - if you want to become a good developer. Go ahead exploring Access, VBA and SQL and the result will be a satisfying application. But take your time. You cannot learn all this within a few weeks! - Nobody can.
Hi starGrabber,
I am still learning from all your writings.
I modified the codes as you said, I deleted the procedure 'cmdAssignPins_Click', renamed the button 'cmdAssignPins' to 'cmdSave' in my form and created a new procedure:
Private Sub cmdSave_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub
I also deleted the procedures 'Form_AfterUpdate' and 'Form_Dirty'
1- The problem is when I click on the first button it adds all the data to the invoice table but the tbl_allPins is not being updated by inv_ID. I am not sure where is the problem.
2- I wanted to add validation rules to Serial_to text box in the form but keeps giving me error. Serial_to must be equal or larger than serial_from all the time. It can not be smaller than serial_from.
maybe this wasn't clear enough, but the button 'cmdSave' should not have the caption 'Assign pins' any longer, as it only saves records now. You should change it to 'Save'.
The reason, why the pin table update doesn't run is the deletion of the procedure 'cmdAssignPins_Click' to which the 'On click' event of the button was linked. Access then removes the entry '[Event Procedure]' in the property field 'On click'. Put it there again and you will see it works fine.
To solve your second problem we make use of the 'BeforeUpdate' event:
Code:
Private Sub txtSerialTo_BeforeUpdate(Cancel As Integer)
' If the rule is broken...
If txtSerialTo.Value < txtSerialFrom.Value Then
' ...advise the user:
MsgBox "'Serial to' has to be equal or larger than 'serial from'!", vbExclamation, "Invalid Input"
' Prevent the update of the textbox:
Cancel = True
End If
End Sub
I am sorry but it does not work. There should be a misunderstanding error.
I deleted the procedure 'cmdAssignPins_Click', renamed the button 'cmdAssignPins' to 'cmdSave' in my form and created a new procedure:
Private Sub cmdSave_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub
After you replied in previous post, I put the procedure 'cmdAssignPins_Click' back into the module and renamed the cmdSave's caption to 'Save', it did not work.
Then I renamed the procedure 'cmdAssignPins_Click' to 'cmdSave_Click()' and still it did not work.
I am confused. Which part I should delete or should not?
My goal is to save the records in the invoices table and update the inv_ID field in tbl_allPins at the same time. Therefore I should not have the 'Save' button anymore. The 'add' button (with a pencil image on the button) should do the both tasks at the same time.
Can you please tell me where is my mistake?
Thank you so much
- I think its purpose is to fill the two textboxes 'invAmount' and 'fee'.