Access 2010 Web Database Help (1 Viewer)

[bergmanc2]

I need some help with a 2010 web database...

I'm trying to figure out how to count the number of records that fit a certain criteria and place the value in an unbound text box (e.g. count the total number of records where the field [TLR4]= "-/-")

In 2007 or even normal 2010 I'd just use DCount in VBA, but web databases don't allow for VBA and I have been unable to get these ridiculous "For Each Record In" macros to work properly.

Any help, thoughts, suggestions?

 

[Beetle]

To do this you are going to need two macros;

First, a Named Data Macro at the table level. Here you will create a parameter and set a Local Variable to 0. Then, use For Each Record loop to find records that match your criteria (use the parameter in your criteria) and increment the Local Variable by one each time (make sure you do this inside the For Each block). Finally, set a Return Variable based on the Local Variable. See the attached DataMacro screen shot.

Second, a standard web Macro where you will use Run Data Macro, give the parameter a value, then use Set Property to set the Value of the unbound control in your form, based on the Return Variable. You would then call this macro from some event on your form. See the attached WebMacro screen shot.

 

[GBalcom]

I'm just curious....Could I use this same approach as a "check" to a login form? I was thinking of using Dcount to ensure the username and password matched in the login form. but can I reference the form control values in the named data macro? , then pass the value back to the macro that is running the form "log in" button?

Thanks,
Gary

Ps. I'm a complete newbie at this, so I'm sorry if this question is rudimentary.

pps. Please see attached screen shot...I'm not sure how to get this to work.

 

[Beetle]

I need a little more background. Do you have a table of User Names and Passwords that you're validating against when a user enters values in a login form? Also, what do you want to happen if it is valid / invalid? Open another form, display a message box, etc?

 

[GBalcom]

Thanks for the fast response....

I will have a table that will store the usernames and passwords, as well as a security level.

If the login information is approved, it would go to my main navigation form. If it is not i would like a message box to show up.

I have the attached VBA Code performing the function I would like:

Private Sub cmdLogin_Click()

Dim UserLogin
Dim intLogonAttempts
intLogonAttempts = 0

'Check to see if data is entered into the UserName combo box

If IsNull(Me.cboUsername) Or Me.cboUsername = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboUsername.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblUserAccess to see if this
'matches value chosen in combo box

If Me.txtPassword.Value = DLookup("ValidationText", "tblUserAccess", _
"[ID]=" & Me.cboUsername.Value) Then

ID = Me.cboUsername.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "F_Login", acSaveNo
DoCmd.OpenForm "F_TestScreen"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If

'Saves UserLogin Variable for future use

UserLogin = Me.cboUsername.Value

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


Private Sub Detail_Click()


End Sub

Private Sub Form_Open(Cancel As Integer)

LogEvent = " Logon Opened "
LogEvt
End Sub


I don't know code...I simply modified the names in this to work for me.

But, I also need to create a logging event. I have this already set up with a datamacro and another macro to call it, but the "on click" event already has this Code in it, so I don't know how to have both work together.

 

[Beetle]

I'm running out of time for today so I'll have to get back to you tomorrow.

 

[GBalcom]

Sean,
I've got quite a bit further on this, but I'm really having trouble coming up with something that will work for verifying the password from the table. I've tried a Dlookup, but they don't seem to work with macros....would it help to pull the correct value from a query?

 

[GBalcom]

Still having issues with this....the only thing that seems to be holding me back is this statement in the Attached Macro, I got the idea from the link below:

http://stackoverflow.com/questions/1790046/login-screen-in-access-with-forms-macros-and-queriesms

 

[Beetle]

OK, sorry it took me a bit to get back to you, I was a busy this morning at work. What I'm going to do is explain how I would handle this in a web database and you can adapt it to your circumstances. Since I don't know much about your application this will be easier than trying to explain to you how to correct what you've got going on your end.

First, this example assumes the following;

1) A table named tblLogin with the fields;

• ID
• UserName
• Password

2) A form named frmLogin with the following Controls

• txtUserName (text box)
• txtPassword (text box)
• cmdLogin (command button)

3) A form named frmSplashScreen that you want to open if the login attempt is successful.

This method uses three macros;

1) A table level data macro to check if a record exists with the User Name and Password supplied by the login form.

2) An embedded macro in the Click event of the command button on the login form to run the data macro.

3) An embedded macro in the Load event of the login form to initialize certain settings.

First, the data macro for tblLogin. This macro uses parameters for the UserName and Password values. This macro sets a ReturnVar (return variable) that is then used by the embedded macro in the form.

Note: You cannot supply the UserName and Password values directly in this macro, you simply create the parameters. The values are supplied later when the data macro is run by the embedded macro in the form.

The data macro looks like;

Next, the embedded macro in the Click event of the command button on the form. This macro uses RunDataMacro to run the table level data macro. The values for the parameters come from the text boxes on this form. If the ReturnVar supplied by the data macro is True, then a matching record was found so we close the current form and open the Splash Screen. If the ReturnVar is False then no matching record was found so a message box is displayed and the form controls are disabled. This macro also increments a counter to track login attempts. This macro looks like;

Last, the embedded macro in the Load event of the login form. This macro initializes the login counter to zero and makes sure that the form controls are enabled in case they were disabled on a previous attempt. This macro looks like;

 

[GBalcom]

Thank you very much for all your help. I've followed it for my database. I believe I'm very close to making it work but there still seem to be an issue. I believe it is in the data macro, but I'm not 100% sure. Please see attached database.

Thanks again!

 

[Beetle]

Maybe we should clarify a few things because I thought we were talking about a web database but that's not what you have here. You have a client database. Now, if you want to, you can still use the new macros in a client database, but if the VBA code that you were using was working fine then I don't see a reason to try to convert it to macros. If you are simply trying to learn how the new macros work so that you can then create a web database, I would still suggest you perform that learning process in an actual web database because there are other differences between the two besides just the requirement to use macros in place of VBA code. For example, there is no Design view for web objects, only Layout view, and the way you design forms and reports is different as well. Forms and Reports are laid out in blocks of screen space, so to speak, which can be re-sized, split, merged, etc. to achieve the layout you want. Basically I'm just saying that when it comes to the new web apps there is more to learn than just the new macro interface.

The following images will give you an idea how to know what you're working with;

A web database;

A Client database;

Regarding your issue, you don't specify what it is but one problem I can see right off hand is that you are using a combo box for the UserName on the login form. Your combo box returns the ID and UserName fields from the Users table. It displays the UserName but it actually stores the ID, therefore any reference to this combo box is actually going to return the ID, not the UserName. That means that the way you currently have your macros written it will never find a matching record because your data macro is looking for the UserName (and Password) but your embedded macro is supplying the ID (from the combo box) as the value for the parameter.

So, you either need to modify the data macro so that it looks for the ID (and the Password), or you need to modify the combo box so that it only returns the UserName (or at least make the UserName the bound column)

 

[GBalcom]

Sean,
Wow...Thank you so much for the excellent post (and your catch on my combo box). I have followed your macro code through and also learned a lot about what is possible. My database is working properly with both the log in screen and a table to track user activity.

As far as why I chose to use the macros. I guess I really want to see what is possible with macros, as I will be creating a Web Database for my next project. I also am not too comfortable with VBA yet.

Thanks for the heads up on how a web database is different. I was under the impression the major difference was the Non-VBA coding.


Thanks again!
Gary

 

[Beetle]

Glad I could help.

 

[big-al]

Hi there,

I am completely new to this forum and in fact to any forum so please be gentle with me!!!

I was really pleased to find your post (detailed below) as it answers my exact question. I am trying to recreate the macros you have shown in a Web Database in Access 2010 but I don't seem to have the same action options as you do. For example I can't find the "Look up a record in" action. I might be missing the blindingly obvious here but would appreciate any help you can give me. Please let me know if you need any further info or me to elaborate further.

Kind regards,

Alistair

OK, sorry it took me a bit to get back to you, I was a busy this morning at work. What I'm going to do is explain how I would handle this in a web database and you can adapt it to your circumstances. Since I don't know much about your application this will be easier than trying to explain to you how to correct what you've got going on your end.

First, this example assumes the following;

1) A table named tblLogin with the fields;

• ID
• UserName
• Password

2) A form named frmLogin with the following Controls

• txtUserName (text box)
• txtPassword (text box)
• cmdLogin (command button)

3) A form named frmSplashScreen that you want to open if the login attempt is successful.

This method uses three macros;

1) A table level data macro to check if a record exists with the User Name and Password supplied by the login form.

2) An embedded macro in the Click event of the command button on the login form to run the data macro.

3) An embedded macro in the Load event of the login form to initialize certain settings.

First, the data macro for tblLogin. This macro uses parameters for the UserName and Password values. This macro sets a ReturnVar (return variable) that is then used by the embedded macro in the form.

Note: You cannot supply the UserName and Password values directly in this macro, you simply create the parameters. The values are supplied later when the data macro is run by the embedded macro in the form.

The data macro looks like;

Next, the embedded macro in the Click event of the command button on the form. This macro uses RunDataMacro to run the table level data macro. The values for the parameters come from the text boxes on this form. If the ReturnVar supplied by the data macro is True, then a matching record was found so we close the current form and open the Splash Screen. If the ReturnVar is False then no matching record was found so a message box is displayed and the form controls are disabled. This macro also increments a counter to track login attempts. This macro looks like;

Last, the embedded macro in the Load event of the login form. This macro initializes the login counter to zero and makes sure that the form controls are enabled in case they were disabled on a previous attempt. This macro looks like;

 

[GBalcom]

Big Al,
Unfortunately, I didn't end up running this as is...I ended up learning more about VBA and going that route. The reason why is that a table level Data Macro does not appear to work with a split database. If I had made this an actual web database then I would have used this method.

The only thing I can tell you is to google data macros and look at how they work.

 

[Beetle]

I am trying to recreate the macros you have shown in a Web Database in Access 2010 but I don't seem to have the same action options as you do.

The actions that are available in a macro depend on the type of macro you're creating and where you're creating it. If you post back with more details about what you're doing I will try to help.

 

[ldowney]

To do this you are going to need two macros;

First, a Named Data Macro at the table level. Here you will create a parameter and set a Local Variable to 0. Then, use For Each Record loop to find records that match your criteria (use the parameter in your criteria) and increment the Local Variable by one each time (make sure you do this inside the For Each block). Finally, set a Return Variable based on the Local Variable. See the attached DataMacro screen shot.

Second, a standard web Macro where you will use Run Data Macro, give the parameter a value, then use Set Property to set the Value of the unbound control in your form, based on the Return Variable. You would then call this macro from some event on your form. See the attached WebMacro screen shot.

I tried the two macros you provided, but am having a problem. In the unbound textbox that is to receive the value returned by the Named Data Macro, instead of a numeric value, I'm getting the string "[ReturnVars]![varTotal]" What might I be doing incorrectly that is causing this?

Thanks for your help!