I was trying to implement the #autocomplete_path to be used to autocomplete URL aliases. I believe this is somewhat a short introduction to doing autocomplete in your textfields when using the Form API.

<?php
function example_autocomplete() {
  $items = array();
  $i = 2;
  while(arg($i))
  {
    $items[] = arg($i);
    $i++;
  }
  $string = implode('/', $items);

  $matches = array();
  if ($string) {
    $result = db_query_range("SELECT dst FROM {url_alias} WHERE LOWER(dst) LIKE LOWER ('%s%%')", $string, 0, 10);
    while ($destination = db_fetch_object($result)) {
      $matches[$destination->dst] = check_plain($destination->dst);
    }
  }

  drupal_json($matches);
}
?>

What it basically does is take advantage of the URL and using the arg function in Drupal.

You'll then need to specify on your hook_menu this:

<?php
function example_menu() {
  $items['example/autocomplete'] = array(  
    'title' => 'Example autocomplete',
    'page callback' => 'example_autocomplete',  
    'access callback' => TRUE,
    'type' => MENU_CALLBACK,
  );

  return $items;
}
?>

You can try visiting the URL example.com/example/autocomplete/path/to/autocomplete to see if it indeed returns the expected URL in json format.

Once you are done, you can now make use of your #autocomplete_path in your form. For example:

<?php
  $form['example'] = array(
    '#type' => 'textfield',
    '#title' => t('Example Autocomplete Path'),
  );

  if (module_exists('path')) {
    $form['example']['#autocomplete_path'] = 'example/autocomplete';
  }
?>

You'll be able to find more information here.

Thanks to Jay Matwichuk for providing me a solution as autocompletion of URL aliases is somewhat tricky.