By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

SQL to return updateable records

P: 8
I am using an SQL statement in a forms record source. This statement distinguishes between departments and returns all the records for that department. The problem is the forms main intention is to allow users to enter new records into the database. This worked before I used the SQL statement simply by the user pressing the >* button, but this button is now not pressable.

Here is my SQL statement

[code]... SELECT * FROM Supervisor, UserInfo WHERE (((UserInfo.DeptID)=+2)) And Supervisor.ID=UserInfo.ID..[code]

I have tried an insert statement but cannot seem to get this to work...Any help would be appreciated. Thanks
Sep 6 '06 #1
Share this Question
Share on Google+
5 Replies


P: 8
Oh yeah here is the vb code for this app

Expand|Select|Wrap|Line Numbers
  1. ....Const ReplString = "+2"
  2.     Dim intInsPt As Integer, strUserMatch As Variant
  3.  
  4.     intInsPt = InStr(Me.RecordSource, ReplString)
  5.     If intInsPt = 0 Then
  6.         ' Oops! SQL template has no ReplString in it!
  7.         Beep
  8.         MsgBox "Form Record Source error. Notify programmer.", vbExclamation
  9.         Cancel = True
  10.         Exit Sub
  11.     End If
  12.     strUserMatch = DLookup("UserInfo.DeptID", _
  13.         "UserInfo", "UserName='" & CurrentUser() & "'")
  14.  
  15.     If IsNull(strUserMatch) Then
  16.         ' Oops! No match for user in UserInfo table
  17.         Beep
  18.         MsgBox "No permission found for " & CurrentUser() _
  19.             & ". Notify programmer.", vbExclamation
  20.         Cancel = True
  21.         Exit Sub
  22.     End If
  23.     ' Now modify the form record source to select
  24.     ' records for this user only
  25.     Me.RecordSource = Left$(Me.RecordSource, intInsPt - 1) _
  26.         & strUserMatch _
  27.         & Mid$(Me.RecordSource, intInsPt + Len(ReplString))
  28.  
  29.  
Sep 6 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Your form is probably based on a query which is not updateable. I would suggest that you create a new form with the purpose of entering data only. You can have it open on a button click event on this form. Tie it to the table or query it used to be tied to. Make sure the forms data entry property is set to yes.



I am using an SQL statement in a forms record source. This statement distinguishes between departments and returns all the records for that department. The problem is the forms main intention is to allow users to enter new records into the database. This worked before I used the SQL statement simply by the user pressing the >* button, but this button is now not pressable.

Here is my SQL statement

[code]... SELECT * FROM Supervisor, UserInfo WHERE (((UserInfo.DeptID)=+2)) And Supervisor.ID=UserInfo.ID..[code]

I have tried an insert statement but cannot seem to get this to work...Any help would be appreciated. Thanks
Sep 6 '06 #3

P: 8
I was thinking that too...I still would not like for the dept to see each others data though and that would be the case with this.... Right? or is there a way to not have any records shown only a blank form that you can input into?
Sep 6 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Changing the Data Entry option to Yes will only show a new record. No other records will be visible.



I was thinking that too...I still would not like for the dept to see each others data though and that would be the case with this.... Right? or is there a way to not have any records shown only a blank form that you can input into?
Sep 6 '06 #5

P: 8
Thanks, I'm heading in a whole different direction now
Sep 6 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.