Open a form to an ID
Experts,
I am having difficult time opening a form to the [LetterOfCreditID] of frmLetterOfCreditAdd (tblLetterOfCredit)after clicking the button to open frmFees. FrmFees should open to the [LetterOfCreditID] of tblLetterOfCredit.
I assume it is something to do with the table's combobox on tblFees. The record is null on tblFees and I assume this is why. I designed tblFees with many comboboxes and a rowsource qry as I want to be able to view the details when opening tblFees.
Seems like a record needs to be added on tblFees first to make it open to [LetterOfCreditID] but I dont know the best way to do this. I also need the deatails such as [LCNo] to populate too on frmFees (tblFees).
If you have any question please let me know. I think it will be clear once the db is opened.
thank you Copy-of-System.accdb
I am having difficult time opening a form to the [LetterOfCreditID] of frmLetterOfCreditAdd (tblLetterOfCredit)after clicking the button to open frmFees. FrmFees should open to the [LetterOfCreditID] of tblLetterOfCredit.
I assume it is something to do with the table's combobox on tblFees. The record is null on tblFees and I assume this is why. I designed tblFees with many comboboxes and a rowsource qry as I want to be able to view the details when opening tblFees.
Seems like a record needs to be added on tblFees first to make it open to [LetterOfCreditID] but I dont know the best way to do this. I also need the deatails such as [LCNo] to populate too on frmFees (tblFees).
If you have any question please let me know. I think it will be clear once the db is opened.
thank you Copy-of-System.accdb
ASKER
Ed, I think I need to force a new record and populate the [LetterOfCreditID] from tblLetterOfCredit. Not sure though but seems like a solution. I know there is code with If me.newrecord then do something and if Dcount of tbl is null then add but add with conditions.
pdvsa,
Use this code for the click event of your cmdFees. You need to change the data type of your LCNo in tblFees into Text considering that the datatype of your LCNo at tblLetterOfCredit is on Text. The code below will insert the LetterOfCreditID and LCNo into tblFees.
If you want to view the value of LCNo in your frmFees, change the combo box into a textbox. And in your tblFees use text in the look up property of LCNo field.
Private Sub cmdFees_Click()
Dim db As DAO.Database, strMyValue As Integer, strMyValue2 As String
intMyValue = Me.LCID
strMyValue2 = Me.LCNo
Set db = CurrentDb
db.Execute "Insert into tblFees (LetterOfCreditID, LCNo) VALUES (" & intMyValue & ",'" & strMyValue2 & "')"
Set db = Nothing
DoCmd.OpenForm "frmFees", acFormDS, , "[LetterOfCreditID]=" & Me.LCID
End Sub
Sincerely,
Ed
Use this code for the click event of your cmdFees. You need to change the data type of your LCNo in tblFees into Text considering that the datatype of your LCNo at tblLetterOfCredit is on Text. The code below will insert the LetterOfCreditID and LCNo into tblFees.
If you want to view the value of LCNo in your frmFees, change the combo box into a textbox. And in your tblFees use text in the look up property of LCNo field.
Private Sub cmdFees_Click()
Dim db As DAO.Database, strMyValue As Integer, strMyValue2 As String
intMyValue = Me.LCID
strMyValue2 = Me.LCNo
Set db = CurrentDb
db.Execute "Insert into tblFees (LetterOfCreditID, LCNo) VALUES (" & intMyValue & ",'" & strMyValue2 & "')"
Set db = Nothing
DoCmd.OpenForm "frmFees", acFormDS, , "[LetterOfCreditID]=" & Me.LCID
End Sub
Sincerely,
Ed
ASKER
Ok... Thank you dearly. Is this a problem that is not related to design but a problem all designers havr to address when opening a form to an id of another when the records are null?
ASKER
HI Ed, that worked pretty nicely. I made the changes. I have a follow up though. It adds every time I go to this frmFees now,which I know this is what I stated in the beginning but now I have to say I dont need it to add each time I open the form. How would I solve this? Maybe a button would be needed on the frmFees to add a record based on the code above? Or maybe there is some other way. Sometimes I woudl just like to view the fees and not add anything. Or maybe if the records were null and I did not want to add anything (meaning someone jus pressed on teh button but not really wanted to add a record) then there coudl be an undo or something liek that.
Let me know what you think about this. You seem to know quite a lot.
pdvsa
Let me know what you think about this. You seem to know quite a lot.
pdvsa
ASKER
Also, where would I place a msgbox stating the "the LC No needs to be entered"? Right now, if the LCNo is null then I get an error.
strMyValue2 = Me.LCNo
or maybe just modify to handle a null might be easier.
thank you.
strMyValue2 = Me.LCNo
or maybe just modify to handle a null might be easier.
thank you.
ASKER
OK I think I got the other stuff. I need some help with adding a strMyValue3 or not sure if it is an int as the cboEndUserID is a combobox with a rowsource qry.
Notice the addition of strMyValue3, and EndUserID. cant figure it out. I have spent a lot of time on it. Help when you can...thx
Private Sub cmdFees_Click()
DoCmd.RunCommand acCmdSaveRecord
Dim db As DAO.Database, strMyValue As Integer, strMyValue2 As String, strMyValue3 As String
strMyValue3 = Me.cboEndUser
intMyValue = Me.LCID
strMyValue2 = Me.LCNo
Set db = CurrentDb
db.Execute "Insert into tblFees (EndUserID, LetterOfCreditID, LCNo, ) VALUES ('" & strMyValue3 & "'," & intMyValue & ",'" & strMyValue2 & "',)"
'db.Execute "Insert into tblFees (LetterOfCreditID, LCNo, EndUserID) VALUES (" & intMyValue & ",'" & strMyValue2 & "','" & strMyValue3 & "')"
Set db = Nothing
DoCmd.OpenForm "frmFees", , , "[LetterOfCreditID]=" & Me.LCID, , acDialog
End Sub
Notice the addition of strMyValue3, and EndUserID. cant figure it out. I have spent a lot of time on it. Help when you can...thx
Private Sub cmdFees_Click()
DoCmd.RunCommand acCmdSaveRecord
Dim db As DAO.Database, strMyValue As Integer, strMyValue2 As String, strMyValue3 As String
strMyValue3 = Me.cboEndUser
intMyValue = Me.LCID
strMyValue2 = Me.LCNo
Set db = CurrentDb
db.Execute "Insert into tblFees (EndUserID, LetterOfCreditID, LCNo, ) VALUES ('" & strMyValue3 & "'," & intMyValue & ",'" & strMyValue2 & "',)"
'db.Execute "Insert into tblFees (LetterOfCreditID, LCNo, EndUserID) VALUES (" & intMyValue & ",'" & strMyValue2 & "','" & strMyValue3 & "')"
Set db = Nothing
DoCmd.OpenForm "frmFees", , , "[LetterOfCreditID]=" & Me.LCID, , acDialog
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry... Missed this one. Will get back with you.
ASKER
OK i tested and it says I have a syntax here:
db.Execute "Insert into tblFees (EndUserID, LetterOfCreditID, LCNo, ) VALUES (" & intMyValue3 & "," & intMyValue & ",'" & strMyValue2 & "')"
This is only when I execute it and not when I debug.
Thank you
db.Execute "Insert into tblFees (EndUserID, LetterOfCreditID, LCNo, ) VALUES (" & intMyValue3 & "," & intMyValue & ",'" & strMyValue2 & "')"
This is only when I execute it and not when I debug.
Thank you
I looked into the record source of your frmFees and you are right your table tblFees must be populated in order to display it in your frmFees. The code you have in command button Fees is ok.
Ed