Create a new record only if phone number isn't found? (1 Viewer)

[Funkbuqet]

I am trying to create a form to log complaints from the public. We frequently receive complaints from the same people over and over. Instead of duplicating data about the complainer I have decided to make a table for just complainer contact info and a table for complaint details tied to each other by an autonumber complianerID. My goal is to be able to enter the complainers phone number into the complaint form and have it autofill all of the contact info if the phone number matches a record on the complainer table and otherwise allow you to create a new record by filling in the contact info feilds.
I have searched all over the internet and have not been able to find a solution. First I treid a subform and couldn't get it to do what I wanted and then I treid a combo box on the main form with a NotInList event opening the Complianer form. But once the complainer form is saved and closed I can't figure out how to get the main form to recognize the new number as an existing one. Any help would be appreciated. Thank you.

 

[Mj-Egerton]

i could be completely wrong here but a recordset search might be helpful
First i would ensure that each Current User(Complainer) has a ID or something you can use to identify them, depending on what you use to search for, this could be telephone or address or just a auto-number field (ID)

Create a form with a textbox and a button, and under the button's click event put this


Dim strSQL1 as string
Dim LRS as DAO.Recordset
Dim db as Database
Dim varComplainer as string

varComplainer = Me.Textbox(use the same name as textbox on form)

strSQL1 = "SELECT Table.[Field-name], Next Table.[FieldName]" & _
' (Or "SELECT * " & _ For all fields in a table)
" FROM Table" & _
" WHERE Table.[ComplainerIDField] =" & varComplainer & " ;"

Set db = CurrentDb()

'Create SQL statement to retrieve value from query
Set LRS = db.OpenRecordset(strSQL1)

'Retrieve value if data is found

'checks to see if a match between the SQL value and the table can be found before the search reaches the end of the file
If LRS.EOF = False Then
'if match is found code here
This would mean they are a previous complainer

Else
'if a match isnt found code here.....
If its a new Complainer you could enter their Info or whatever into a field using SQL INSERT statement here

End If

'close the recordset
LRS.Close
'clear the recordset
Set LRS = Nothing


Im unsure about how you would go about the autofill, i think thats a java thing

PS this is in VB so dont be stupid and just copy/paste, you will need to enter field names etc yourself
and remember this could be wrong so keep a backup or something
hope this helps

 

[Funkbuqet]

Thank you. I will try and giv this a shot later today. For auto fill I think I can use DLookup() to fill in the correct data corresponding to the ComplainerID#.

 

[Mj-Egerton]

O i am sorry autoFill, i thought you said autoComplete

If LRS.EOF = False Then
'if match is found code here
This would mean they are a previous complainer
SQL SELECT/DLOOKUP- GO HERE <<<<<
Else

i think a DLookUp or a Select SQL would go here making all your form controls (Textboxes etc) equal to their respective values

Name = Table.Name for example

 

[Funkbuqet]

Ok, I entered the code you provided in and it is getting stuck when it tries to run the SQL statement. It give me an error stating "Syntax error with FROM clause" and when I go to debug the line "Set LRS = db.OpenRecordset(strSQL1)" hilighted. So I cut and copied the SQL statement into a blank query (substituting the number I was testing with varRP) and the query works perfectly! Why would it return a FROM syntax error in VBA but the same SQL statement seems to work OK when I put it into a query. I have posted the code I used below.

Private Sub RPphone_AfterUpdate()
Dim strSQL1 As String
Dim db As Database
Dim LRS As DAO.Recordset
Dim varRP As String

varRP = Me.RPphone

strSQL1 = "SELECT ReportingParties.[RPcontactnumber], ReportingParties.[RPid] " & _
"FROM ReportingParties " & _
"WHERE ReportingParties.[RPcontactnumber] ='" & varRP & "' ;"

Set db = CurrentDb()

Set LRS = db.OpenRecordset(strSQL1)

If LRS.EOF = False Then
Me.RPid = DLookup("[RPid]", "ReportingParties", "RPcontactnumber = '" & Me.RPphone & "'")
Else
DoCmd.OpenForm ("ReportingPartiesForm")
End If

LRS.Close

Set LRS = Nothing

End Sub


Note: The code under the If Then statement is just placeholders for now to tell me that the rest of the code is functioning as it should.

 

[Mj-Egerton]

swap

"FROM

for

" FROM

and

"WHERE

for

" WHERE

try that (the difference is the added spaces just so you know)
you usualy put spaces between the initial speach marks and the SQL command(from, where etc) EXCEPT for the initial "SELECT

 

[Funkbuqet]

Nevermind it seems to be working fine now. I din't change anything though. Now I just need to work on getting it to do exactly what I want in each case of the If Then.

Is ther a was to exit the sub if the RPphone feild is cleared. Right now if I enter a number in and then delete it returns the error "Invalid use of Null" and varRP = Me.RPphone is hilighted in the debugger. I tried placing "If Me.RPphone Is Null Then Exit Sub" (Returns Object required error) and "If Me.RPphone = "" Then Exit Sub" (Absolutely no effect) above varRP = Me.RPphone, but neither one worked.

 

[Funkbuqet]

you usualy put spaces between the initial speach marks and the SQL command(from, where etc) EXCEPT for the initial "SELECT

Now that I think about it I did add a space at the end of the Select and From lines, initially it didn't seem to do anything but it must have made the difference.

 

[Mj-Egerton]

Nevermind it seems to be working fine now. I din't change anything though. Now I just need to work on getting it to do exactly what I want in each case of the If Then.

Is ther a was to exit the sub if the RPphone feild is cleared. Right now if I enter a number in and then delete it ti returs the error "Invalid use of Null" and varRP = Me.RPphone is hilighted in the debugger. I tried placing "If Me.RPphone Is Null Then Exit Sub" (Returns Object required error) and "If Me.RPphone = "" Then Exit Sub" (Absolutely no effect), but neither one worked.

hmm not quite sure what you are after? but maybe this might help

Otherwise, underneath the variable declarations (dim var as string, etc)
put

If IS NULL (Me.RPhone.Value) Then

After Line "Set LRS = Nothing" put

Else
Msgbox No Number Entered. Exiting Routine
End IF

is this what you are after?

 

[Mj-Egerton]

Comment removed

 

[boblarson]

Sorry but this, which you have said is wrong, is totally acceptable and how I do it. I never use spaces before the verbs.

"SELECT * " & _
"FROM Table " & _
"WHERE A = X;"

 

[Mj-Egerton]

Sorry but this, which you have said is wrong, is totally acceptable and how I do it. I never use spaces before the verbs.

"SELECT * " & _
"FROM Table " & _
"WHERE A = X;"

really? i was taught to put spaces before the verbs, or is it just a programmer style thing?
anyway ive removed the previous comment to avoid confusiong

 

[boblarson]

So, for example, if I paste this query into code:

then I format it like this:

"SELECT YourTableNameHere.IDField, YourTableNameHere.DateField1, YourTableNameHere.DateField2, YourTableNameHere.AnotherFieldHere, DateDiff("d",[DateField1],[DateField2]) AS DiffBetweenDates " & _
"FROM YourTableNameHere " & _
"WHERE (((YourTableNameHere.AnotherFieldHere) = 'SomeValue')) " & _
"ORDER BY YourTableNameHere.DateField2;"

 

[boblarson]

really? i was taught to put spaces before the verbs, or is it just a programmer style thing?

I've never heard of that rule before. But one benefit it does do is to make the spaces more prominent so you don't think you have one and don't. But either way is fine.

 

[Mj-Egerton]

It must have been one of my teachers preference's then as we were taught to code SQL straight into VBA not take it from the query builder, there isnt much difference in content apart from the obvious formatting etc, thanks for clearing that up bob anyway better end that small convo there before it looks like thread jacking

 

[Funkbuqet]

When I enter "If Is Null (Me.RPphone.Value) Then" it hilights Is and tells me and expression is required. If I reverse it the "If Me.RPphone.Value Is Null Then" it hilights the whole line and tells me it is missing an object. What am i doing wrong?

 

[Mj-Egerton]

When I enter "If Is Null (Me.RPphone.Value) Then" it hilights Is and tells me and expression is required. If I reverse it the "If Me.RPphone.Value Is Null Then" it hilights the whole line and tells me it is missing an object. What am i doing wrong?

The IF IS NULL syntax is correct, it should be before the object name
would it be possible to upload your DB?
if you'd prefer to send it private to just me then i can supply my hotmail
if youd prefer not to post thats cool with me

just a thought try adding END IF directly after the THEN

So

 If Is Null (Me.RPphone.Value) Then
End If
Else
-Rest of the code
End If
end sub

(do not add the end sub i was using it as a reference)

 

[boblarson]

Sorry to have to correct again but it is:

If [COLOR=red][B]IsNull([/B][/COLOR]Me.RPphone.Value) Then

 

[Mj-Egerton]

Sorry to have to correct again but it is:

If [COLOR=red][B]IsNull([/B][/COLOR]Me.RPphone.Value) Then

No that's fine bob you go right on ahead. I'm here learning, like the rest

 

[Funkbuqet]

Excellent. Thank you very much gentlemen, the code is working just like I want it to! I appreciate all your help. I have posted the working code below in case someone in the future comes accross a similar issue.

Private Sub RPphone_AfterUpdate()
Dim strSQL1 As String
Dim db As Database
Dim LRS As DAO.Recordset
Dim varRP As String

If IsNull(Me.RPphone.Value) Then
RPid = Null
Exit Sub
End If

varRP = Me.RPphone

strSQL1 = "SELECT ReportingParties.[RPcontactnumber], ReportingParties.[RPid]" & _
" FROM ReportingParties" & _
" WHERE ReportingParties.[RPcontactnumber] ='" & varRP & "' ;"

Set db = CurrentDb()

Set LRS = db.OpenRecordset(strSQL1)

If LRS.EOF = False Then
Me.RPid = DLookup("[RPid]", "ReportingParties", "RPcontactnumber = '" & Me.RPphone & "'")
Else
DoCmd.OpenForm ("ReportingPartiesForm")
End If

LRS.Close

Set LRS = Nothing


End Sub