Check if record exists before inserting from form

[ierazo]

The problem I have is, that I need to insert an apointment into de database but first I need to verify if there is a record or an apointment in that room that day the same hour, if that is so send a msgbox saying an apointment already exist in that room this day at this hour.

i tried using dlookup but it only works in one record using just one criteria, i need help..... :banghead:

 

[vbaInet]

Can we see the DLookup() code?
By the way, welcome to the forum

 

[GinaWhipp]

Hmm, this might help...
http://answers.microsoft.com/en-us/...-overlap/438dd3ad-0b6b-462d-adb7-e85bdba36138

 

[ierazo]

Private Sub Submit_Click()
Dim row0 As String
Dim row1 As Date
Dim row2 As Date
Dim row3 As Integer
Dim row4 As Integer
Dim row5 As String
Dim row6 As String
Dim query As String




'verifica si los campos estan vacios
If IsNull(Me.practicante_box.Value) Or _
IsNull(Me.hora_box.Value) Or _
IsNull(Me.fecha_box.Value) Or _
IsNull(Me.modulo_box.Value) Or _
IsNull(Me.cubiculo_box.Value) Or _
IsNull(Me.programa_box.Value) Then

MsgBox "Todos Los Campos Deben Estar Llenos", vbInformation, "Información Incompleta"

'si no estan vacios entonces procede a guardar la data de los inputs
Else

row0 = Me.practicante_box.Value
row1 = Me.fecha_box.Value
row2 = Me.hora_box.Value
row3 = Me.modulo_box.Value
row4 = Me.cubiculo_box.Value
row5 = Me.programa_box.Value
row6 = Nz(Me.comentario_box.Value)

if IsNull(DLookUp("[fecha]","Citas","[fecha]=# " & row1 & " #")) = true then

msgbox "Record already exists"

Esle

'DoCmd.SetWarnings False
'DoCmd.RunSQL "INSERT INTO Citas ([practicante],[hora],[fecha],[modulo],[cubiculo],[programa],[comentario])" _
& " VALUES ('" & practicante_box.Value & "','" & hora_box.Value & "', '" & fecha_box.Value & "', '" & modulo_box.Value & "', '" _
& cubiculo_box.Value & "', '" & programa_box.Value & "', '" & Nz(comentario_box.Value) & "' )"
'Forms!Main.List36.Requery


end if
End If

 

[ierazo]

the problem is that I need to check 3 values before insert

 

[GinaWhipp]

Hmm, well this might be a little easier...

Use this...
http://www.access-diva.com/vba13.html

You can then use the Tag property to mark which fields are Required. If they are not filled in you can throw up a message box and if they are run your lINSERT line. Example of how is also on the page.

 

[ierazo]

im not that pro and i think i dont understand...

 

[GinaWhipp]

Well, would you like to try or would you prefer to see if another solution comes along. We are here to help you thru it...

 

[vbaInet]

Looks like Gina has given you some validation code.

For the record check part, you're better off using a DCount() function:

If Nz(DCount("*", "TableName", "Field1 = " & Me.Textbox1 & " AND Field2 = " & Me.Textbox2), 0) = 0 Then

http://www.techonthenet.com/access/functions/domain/dcount.php

Note how Text and Dates are handled in the function.

For the INSERT part you need to use the full reference to the control in the SQL so that it handles Null efficiently.

DoCmd.RunSQL "INSERT INTO Citas ([practicante],[hora],[fecha],[modulo],[cubiculo],[programa],[comentario])" _
& " VALUES ([COLOR="Blue"]Forms!FormName!FieldName[/COLOR], ...etc

 

[ierazo]

WOW!!!!!! vbaInet you just saved my life!!!!!! really apreciated what you guys have done, THANX trully, my respect to you guys!!! blessings!

 

[vbaInet]

Pleased to hear you got it working.