OLD SOURCE CODE need Help!!!

[war6000]

this is the code i have and i want to know what it does so i can make a query from it

Private Sub cmdCheck_Click()
Dim q As QueryDef
Dim rs As Recordset
Dim CountStr As String
Dim Count As Long
    If Len(txtFirstName) = 0 Then
        MsgBox "Please enter a first name", vbCritical, "LegalSOFT"
        Exit Sub
    End If
    
    If Len(txtLastName) = 0 Then
        MsgBox "Please enter a last name", vbCritical, "LegalSOFT"
        Exit Sub
    End If
    
    
    Set q = CurrentDB.QueryDefs("int_qGetCodeCount")
    q.Parameters("pCode") = UCase(MakeCode)
    Set rs = q.OpenRecordset
    
        If rs.RecordCount = 0 Then
            Count = 1
        Else
            Count = rs("MaxCode") + 1
        End If
    CountStr = Format(Count, "000")
    txtCode = UCase(MakeCode) & CountStr
    cmdSave.Enabled = True
    
    
    
End Sub

thanks

[Magestickown]

Maybe you should've read it and the page you copy and pasted it from?

[TysonLPrice]

I'm not familiar with MS Access but it's doing some validation on the first and last name and setting up an update. Do you have a specific question about it?

[baja_yu]

If Len(txtFirstName) = 0 Then

checks the length of the string (Text) in the textbox named txtFirstName. If it is 0, it means it's empty and it gives the error message and aborts/exits the procedure.

[war6000]

i have an already compile version of the software but is using newer code and i just have the original (first) source code.

this is what the software does

we enter "last name" and "fist name"
ex.
"Jackson" "Michael"

the software then makes a code out of it
something like this
JACKMI001

the software looks for a query on the database and if there is another client with the same code (JACKMI001) then it adds a 1 to the counter which ends up like
JACKMI002

the software is suppose to do this but i cant (idk) how to make a query to do this or the software do this.

again thanks for your time and help

[Chrissy]

I'm confused at what your question is... what you just described above seems to be exactly what the code is doing. What exactly is your issue?

[war6000]

I dont have the query on my database and i dont khow how to make one that would give me the result the code is asking me for

Thanks

[Chrissy]

Here is some generic code... You would have to know the table name the query is referring too.

Private Sub cmdCheck_Click()
Dim q As QueryDef
Dim rs As Recordset
Dim CountStr As String
Dim Count As Long
    
If Len(txtFirstName) = 0 Then
    MsgBox "Please enter a first name", vbCritical, "LegalSOFT"
    Exit Sub
End If
    
If Len(txtLastName) = 0 Then
    MsgBox "Please enter a last name", vbCritical, "LegalSOFT"
    Exit Sub
End If
 
'Assuming that MakeCode is set somewhere else???
 
rs.Source = ("SELECT MAX(pcode) as ""MaxCode"" FROM [table_name] WHERE pcode = '" + UCase(MakeCode) + "'")
 
rs.Open
 
If rs.RecordCount = 0 Then
    Count = 1
Else
    Count = rs!MaxCode + 1
End If
 
rs.close
 
CountStr = Format(Count, "000")
txtCode = UCase(MakeCode) & CountStr
 
cmdSave.Enabled = True
   
End Sub

[war6000]

so by doing this how would the query look like

[Chrissy]

This is the query... Instead of putting it in the database, you can just add it in the code.

("SELECT MAX(pcode) as ""MaxCode"" FROM [table_name] WHERE pcode = '" + UCase(MakeCode) + "'")

You need to fill the appropriate table name in. I can't be more specific because I don't know what your database looks like. Basically this should return the maximum value of pcode in the database.

[war6000]

where do i place this

[Chrissy]

Did you try the code I gave you? The query is in there... you need to replace the [table_name] with the actual name of the table.

[war6000]

I tried it already

i removed this part and added yours

Set q = CurrentDB.QueryDefs("int_qGetCodeCount")
    q.Parameters("pCode") = UCase(MakeCode)
    Set rs = q.OpenRecordset

[Chrissy]

Are you getting an error? I can't help you if you are not specific...

[war6000]

i get an error when i click check

i can print screen the error if u want or stream it

[war6000]

this is the error i'm getting

[war6000]

compilation error
Could not find the method or data member

the version we have here is in spanish

[war6000]

this is the code i have. the table is "tblCustomer"

[war6000]

this is what i get on Visual Basic after the compilation error

[Chrissy]

Can you show me the references that are in your project?

[Chrissy]

You also need to set the recordset to the connection... Add this before rs.Source. I don't think it will fix your problem, but you will need it regardless.

Set rs = New Recordset
'Conn is whatever your connection object is defined as.
rs.ActiveConnection = Conn

Also you may need to ad a "Group By" clause in the SELECT statement... not sure.

[war6000]

which references. database location or what

[Chrissy]

Under Project >> References... which references do you have added to your project. You can just show me a screen shot.

[war6000]

here is the reference. Sorry i took long

[Chrissy]

Try selection the Microsoft ActiveX Data Objects Recordset Library too. Then try this for your code.

Private Sub cmdCheck_Click()
Dim q As QueryDef
Dim rs As ADODB.Recordset
Dim CountStr As String
Dim Count As Long
    
If Len(txtFirstName) = 0 Then
    MsgBox "Please enter a first name", vbCritical, "LegalSOFT"
    Exit Sub
End If
    
If Len(txtLastName) = 0 Then
    MsgBox "Please enter a last name", vbCritical, "LegalSOFT"
    Exit Sub
End If
 
Set rs = New ADODB.Recordset
 
'Conn is whatever your connection object is defined as... is that CurrentDB?
rs.ActiveConnection = Conn
 
rs.Source = ("SELECT MAX(pcode) as ""MaxCode"" FROM tblCustomer WHERE pcode = '" + UCase(MakeCode) + "'")
 
rs.Open
 
If rs.RecordCount = 0 Then
    Count = 1
Else
    Count = rs!MaxCode + 1
End If
 
rs.Close
 
CountStr = Format(Count, "000")
txtCode = UCase(MakeCode) & CountStr
 
cmdSave.Enabled = True
   
End Sub

[war6000]

Thanks for the help but its not working
I added the reference u told me but had no luck and got the compilation error

i think i can make it work if i figure out how to make query on access

i need a query that counts the amount of
code names without the numbers at the end

ex

AGUIAN001
AGUIAN002
AGUIAN003

COUNT ONLY THE AMOUNT OF
AGUIANXXX

[TysonLPrice]

MS SQL
select count(*) from YourTable where substring(ColumnName,1,6) = 'Aguian'

[Chrissy]

I don't think you want to use Count(). You are then assuming that all the AGUIAN records are in order starting at one. This would also assume that no records were deleted. I think you should use the MAX() function. This will return the last number used... You are basically creating the primary key for a record correct?

The following SQL statement also assumes that pcode is the name of the field that holds the data you are referencing.

select MAX(pcode) from tblCustomer where pcode LIKE 'AGUIAN*'

[war6000]

'Conn is whatever your connection object is defined as.

what would this look like?

like this
rs.ActiveConnection = CurrentDB ??????

[Chrissy]

I can't see how CurrentDB is defined... is it defined as a Connection? If so, that would be correct.