Invalid user of Null: Runtime error 94. Getting error when textbox is Blank. How to handle this error:

[pedie]

Original code. username below is textbox name.
Please help.

[/FONT]
[FONT=Courier New]Private Sub Command9_Click()
If Me.Username.Value = "" Then MsgBox "Username cannot be blank", vbInformation, "OK?": Exit Sub
MsgBox Me.Username.Value
End Sub

 

[dogdays]

Try

    if len(Me.Username.value & "") = 0 then
        msgbox "Username may not be blank.", vbinformational
        Me.Username.Setfocus
        exit sub
    end if

Jack

 

[alansidman]

Pedie,
Perhaps an alternative. If your form is bound to a table and you want to ensure that the field username is not null, then in your table properties for the filed, set it to required.

Alan

 

[james_lankford]

    If Len(Nz(Me.Username.Value)) = 0 Then
        MsgBox "Username may not be blank.", vbInformation
        Me.Username.SetFocus
        Exit Sub
    End If

 

[pedie]

Thanks Alan, Dogsday and thanks James

Thanks alot and may I also ask, what is nz in that code line?

And one more thing how do i make the code select the table called "Pedie" and and then update username and date?
I know this can be very easy for you guys, I need to know how update things works here.

like in excel example..

[/FONT]
[FONT=Courier New]Sheet1.activate[/FONT]
[FONT=Courier New]range("A1").value = Username.value[/FONT]
[FONT=Courier New]range("B1").value = Date.value[/FONT]
[FONT=Courier New]

Thanks agan for helping.

 

[xenou]

NZ() is explained in Access help - in short, if a value is Null it will substitute a default value of "" for the Null.

NZ(Null) --> returns ""
NZ(Null, 0) --> returns 0
NZ(Null, "Mary Poppins") --> returns "Mary Poppins"

If the value passed to NZ is not Null, it remains as it is:
NZ("Pedie","Mary Poppins") --> returns "Pedie"

Updating a table can be done in many different ways. If the control is bound to the table (simplest) then changes are immediate and automatic. The only trouble here is sometimes updates occur when you don't expect them. I like to (suggest to) make all fields required. So nothing can ever be committed to a table unless it is a complete entry with all fields filled in.

 

[pedie]

Xen, thanks for explaining things in detail.
About update table, I want to try on the code and not by using the query.

how can i make my code refer to table or records and fields...Please give me a start.


is it something like this?

[/FONT]
[FONT=Courier New] table("Pedie").Usernames.value = Forms("MyForm").Username.value[/FONT]
[FONT=Courier New]

Thanks again.


And is there a site or sample codes i can refer to?

 

[xenou]

It depends - do you want to Add a new record or Change (update) an existing record?

(It's better to learn Access without code at first ... forces you to learn how Access works and saves you from writing so much code in the long run. That said, you can write SQL scripts or use DAO recordsets to work with your data in tables and forms).

 

[xenou]

DAO examples:
http://allenbrowne.com/func-dao.html
http://allenbrowne.com/ser-29.html
http://www.classanytime.com/mis333k/sjdaoadorecordsets.html

I can't remember how I learned all this. One day at a time. I read a book about Access. Took a class. Read a book about SQL and database design. Started working on databases. Read some more. And more. It's way too much to explain all at once. Never bothered much with security (sigh). You must know SQL or you're dead, though.

 

[pedie]

Xen, I am still going through it...very interesting this is type of code examples i need. Thank you soo very much!!!


Just tried this code and works perfect!

Hey Xenou, I am not getting the example of how to update it in different database. Suppose i have a form in database1 and want to add the records to database2 then how is this achieved?

I know I have asked to many things but if you have time, please help me with this one too.


Thanks again soo much!

[/FONT]
[FONT=Courier New]Option Compare Database
Option Explicit[/FONT]
[FONT=Courier New]Sub TryLoop()
Dim rs As DAO.Recordset
Dim ID As Object
Dim LN As Object
' open the DAO or ADO recordsets as shown above (Workstations only)[/FONT]
[FONT=Courier New]Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)[/FONT]

[FONT=Courier New]Set ID = rs("Names") ' open the object container for these fields
Set LN = rs("Dates")[/FONT]
[FONT=Courier New]Do While Not rs.EOF
    Debug.Print ID; LN ' compare this syntax to the usual syntax in the example below
    rs.MoveNext
Loop
End Sub