cheers for hints
Minty just check but when the combo boxes are empty doesnt prompt with the message to select something, ill have a sus tomorrow i need sleep lol.... let me know if ya think of anything, cheers for your help.
cheers for hints
Minty just check but when the combo boxes are empty doesnt prompt with the message to select something, ill have a sus tomorrow i need sleep lol.... let me know if ya think of anything, cheers for your help.
PMFJI,
You have the delete command in the wrong place.
If you select "NO" to the question "Are you sure you want to Delete?.", what happens? You drop out of the SELECT CASE construct , then the delete code executes.
Here is how I would write the code (modifying Minty's code )
Code:Private Sub cmdDeleteJoinery_Click() Dim iAccLvl As Integer Dim Response As Integer *** 'Note that ALL declarations are together at the top of the routine!! *** Dim delJoinery As Integer iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) Select Case iAccLvl Case 1, 2, 3 Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo) If Response = vbNo Then 'no selected ' do nothing Else 'yes selected ' do something If IsNull(Me.cboJoinery) Then MsgBox "Please select Joinery Unit" Me.cboJoinery.SetFocus Exit Sub Else delJoinery = "DELETE * FROM tblJoineryUnit WHERE JoineryID_PK = " & Me.cboJoinery & ";" ' Debug.Print delJoinery CurrentDb.Execute delJoinery, dbFailOnError 'now requery the combo box Me.cboJoinery.Requery Me.cboJoinery = Me.cboJoinery.ItemData(0) End If End If Case Else MsgBox "You Do Not have Access", vbOKOnly End Select End Sub
You should be able to step through the code in your mind to see if the code does what you want.
If you have problems (I do - a lot), then you need to single step through the code.
For debugging suggestions, see Debugging VBA Code
Good luck with your project.......
thanks for that ssanfu.PMFJI,
You have the delete command in the wrong place.
If you select "NO" to the question "Are you sure you want to Delete?.", what happens? You drop out of the SELECT CASE construct , then the delete code executes.
Here is how I would write the code (modifying Minty's code )
Code:Private Sub cmdDeleteJoinery_Click() Dim iAccLvl As Integer Dim Response As Integer *** 'Note that ALL declarations are together at the top of the routine!! *** Dim delJoinery As Integer iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) Select Case iAccLvl Case 1, 2, 3 Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo) If Response = vbNo Then 'no selected ' do nothing Else 'yes selected ' do something If IsNull(Me.cboJoinery) Then MsgBox "Please select Joinery Unit" Me.cboJoinery.SetFocus Exit Sub Else delJoinery = "DELETE * FROM tblJoineryUnit WHERE JoineryID_PK = " & Me.cboJoinery & ";" ' Debug.Print delJoinery CurrentDb.Execute delJoinery, dbFailOnError 'now requery the combo box Me.cboJoinery.Requery Me.cboJoinery = Me.cboJoinery.ItemData(0) End If End If Case Else MsgBox "You Do Not have Access", vbOKOnly End Select End Sub
You should be able to step through the code in your mind to see if the code does what you want.
If you have problems (I do - a lot), then you need to single step through the code.
For debugging suggestions, see Debugging VBA Code
Good luck with your project.......
yeah i've only just got my head a little around declarations and how to use them, can you explain this part for me -
' Debug.Print delJoinery
CurrentDb.Execute delJoinery, dbFailOnError
If you un-comment the line above, the value of the variable "delJoinery" will be printed in the immediate window.Code:' Debug.Print delJoinery
"CurrentDb.Execute" executes an action query, WITHOUT having to set "Warnings Off" and "Warnings On" (I never use DoCmd.RunSQL).Code:CurrentDb.Execute delJoinery, dbFailOnError
(see help or Goggle)
Action queries are queries that can add, change, or delete (as in Append, Update, Delete) multiple records at one time.
cheers ssanfu for explaining.
I wonder if you guys can help me with this one, with using the codes above ive put in a hyperlink to open an existing database.
I managed to get it working great thanks to Minty and Ssanfu, both database are pretty much replica's. i'm wanting to take the user name and password from the initial db and when the 2nd one is open transfer the details so that person is logged in and even if i can manage to open at the record i want to open, this is my code so far -
Code:Private Sub cmdReceivalOfGoods_Click() Dim iAccLvl As Integer Dim Response As Integer 'Note that ALL declarations are together at the top of the routine!! Dim delStage As String iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) Select Case iAccLvl Case 1, 2, 3, 4 Response = MsgBox(prompt:="Does the Order Number Have 'CT' at the start?.", Buttons:=vbYesNo) If Response = vbNo Then 'no selected DoCmd.OpenForm "ReceivedGoods" Exit Sub Else 'yes selected ' do something Response = MsgBox(prompt:="This Order Number is in the Old database, do you wish to Open the Old Database?.", Buttons:=vbYesNo) If Response = vbYes Then Me.txtHyperLink2.Hyperlink.Follow DoCmd.Quit Else ' Do Nothing Exit Sub End If End If Case Else MsgBox "You Do not have Permissions", vbOKOnly End Select End Sub
Simply link the tables from the old database and build a form to query the old records.
Hopping in and out of databases is not very efficient, and probably in this case not required.
Keep things simple.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
I know and can tell the way i have it set up is not to efficient at the moment especially with hyperlinks, but that seems like a lot of work to do create forms to go between dbs as the way orders are placed on the 2 db's are different this is why i started a fresh start and I just wanted to tap into the old db when needed. hoping in 2-3 months the old db would only be used to look up material used for jobs if needed.
so your saying have the new tables linked and also old tables linked? then i would be doubling up,
apologies if im not getting this, im trying to understand the concept of what your saying and how to put into practice
Lets take a step back and get the full picture here.
Say you have DatabaseOld with a table called Jobs, do you have the same table / structure and table name in DatabaseNew?
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
my tables are similar and same names but new db has a few new fields in some of the tables.
there are 2 extra tables which have edited my relationship from the old db.....
old db - tbljobs relationship to tblJoinery relationship to tblOrders relationship to tblOrderDetails relationship to tblOrderNumbers = 1 to many relationships...... which i changed to
New db - tbljobs relationship to tblStage_BuildingName relationship to tblAddress relationship to tblOrderDetails relationship to tblOrderNumbers = 1 to many relationships
How often do you envisage referring to the old data? This all comes down to time/effort/usage sums on your behalf really.
if it's not often, I would simply open the old database and make it read only, I wouldn't quit out the new database, you'll probably still need users in it.
Change the old db forms slightly (nasty big red OLD labels ) to make it obvious that it's the old system.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
down the track give 6-7 months not much, would be for a select few of 3-4 people to look up an old job to see what material was used previosly if we need to match it.
the new database contains all jobs we haven't ordered any material for which means no Joinery units have been recorded. so until the jobs that have stuff ordered for finish guys will have order more material through the old db this is a little cumbersum but only for a period until we ween out the finished jobs.
i have prep the guys so i think its best just to leave and as you said put some big labels on it clearly marking OLD DB.
by the way i have changed all my if statements through out to the codes you and ssanfu helped me with and all works bloody awesome!!!
cheers for the help